How to Use SAS - Lesson 3 - Importing External Data

Share
Embed
  • Published on Oct 18, 2011
  • Bitcoin donations are welcome: 1GGV3gbJeA83FWmz9hDfPri8EuqcUtodXy
    Mike's SAS Tutorials Lesson 3
    Update: Many users have expressed an interest in using my cheat sheet. Ultimately, my goal was to encourage users to create their own cheat sheets to learn from. However, if you'd like to use mine, it is available for purchase at the following web page using PayPal:
    www.e-junkie.com/ecom/gb.php?i=1077465&c=single&cl=207650
    This video series is intended to help you learn how to program using SAS for your statistical needs. Lesson 3 introduces the concept of permanent or external data sets and how to import them into SAS. I provide basic methods of importing permanent data sets using the INFILE statement and the IMPORT procedure (PROC IMPORT) for non-SAS based data files. I also discuss libraries and the LIBNAME statement to import SAS data directly using the SET statement. Finally, I show how one can save a SAS data set from the data step using LIBNAMEs in the DATA step.
    Helpful Notes:
    1. The LIBNAME statement is used to point SAS towards a specific folder on your computer.
    2. The INFILE statement "reads" data into SAS if it is of a certain format (usually comma, space, or tab delimited).
    3. PROC IMPORT - imports data of any of several different file formats into SAS.
    4. The SET statement imports data from a library into SAS at the DATA STEP.
    5. The library name in a data step's data name "writes" data from SAS into your library folder using SAS's own file format system.
    Today's Code:
    data main;
    input x y z;
    cards;
    1 2 3
    7 8 9
    ;
    run;
    proc contents data=main;
    run;
    proc print data=main;
    run;
    /* TEMPLATED CODE: .txt file type, with or without delimiters */
    data [appropriate data set name here];
    infile "[your file location here, including .txt extension]"
    LRECL=[a logical length of your data to emcompass ENTIRE data] DLM=',';
    input
    [variable names here]
    ;
    run;
    data infile_main;
    infile "C:\My SAS Files\main.txt";
    input x y z;
    run;
    proc print data=infile_main;
    run;
    /* TEMPLATED CODE: Microsoft Excel (.xls) file type */
    proc import out=[your data set name here]
    datafile='[your file location here, including .xls extension]'
    dbms=excel replace;
    *Optional statements are below;
    sheet='[specify sheet to obtain]';
    getnames=[yes/no - first row = variable names];
    mixed=[yes/no - refers to data types, if num AND char varibles, use yes];
    usedate=[yes/no - read date formatted data as date formatted SAS data];
    scantime=[yes/no - read in time formatted data as long as variable is not date format];
    run;
    proc import out=imported_excel
    datafile='C:\My SAS Files\main.xls'
    dbms=excel replace;
    *Optional statements are below;
    sheet='Sheet1';
    getnames=yes;
    run;
    proc print data=imported_excel;
    run;
    libname home "C:\My SAS Files\";
    data sas_format; set home.main;
    run;
    data home.sas_format; set infile_main;
    run;

Comments • 145

  • Carolyne Cody Bennett
    Carolyne Cody Bennett 11 days ago +1

    Has anyone paid and received the cheat sheet recently? I want to make sure it still works before purchasing it.

  • colton donathan
    colton donathan 8 months ago +1

    this guy is a baller!

  • MasterofPlay7
    MasterofPlay7 9 months ago

    can you import excel or csv files using data step as well? thx!

    • MasterofPlay7
      MasterofPlay7 9 months ago +1

      thx bro, much appreciated!

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  9 months ago +1

      Check out the rest of the video series - your questions are answered in Lesson 5 and some of my SAS in 60 seconds videos :)

    • MasterofPlay7
      MasterofPlay7 9 months ago

      also how can you add a row label to the existing dataset (eg the column name)?

    • MasterofPlay7
      MasterofPlay7 9 months ago

      but can you actually change variables name using proc import? for eg you have raw data without the headings, you want to add a column labelling the raw data

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  9 months ago

      CSV, definitely. You'll want to use a infile statement with a delimiter statement and input statements. As for Excel, I don't think so. Why not use PROC IMPORT?

  • MasterofPlay7
    MasterofPlay7 9 months ago

    how many characters can you put on libname? Is there a restriction? thx!

    • MasterofPlay7
      MasterofPlay7 9 months ago

      thx bro!

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  9 months ago +1

      Hard to tell exactly how to solve your issue, but I would check out this link: support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm

    • MasterofPlay7
      MasterofPlay7 9 months ago

      but if i put [libname mylib "path";] works?

    • MasterofPlay7
      MasterofPlay7 9 months ago

      but how come I put [libname SASOURCE "path";] it says libname is not a valid sas name?

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  9 months ago

      The libname itself can be up to 8 characters in length. The path to the directory can be up to 32,767 characters long, I THINK. Would need someone to confirm that for me. Cheers!

  • Claude Daiga
    Claude Daiga 10 months ago

    Hi Mike. How does one import data from all the available spread sheets(with the same schema) in an excel file programmatically?

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  10 months ago

      @Claude Daiga My pleasure. Hope it helps you out!

    • Claude Daiga
      Claude Daiga 10 months ago

      Thanks very much Mike.

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  10 months ago +1

      Check out this github repo: github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

  • Berns Buenaobra
    Berns Buenaobra Year ago

    Thank for this video Mr.Mike (wow since 2011) it made me explore the tool for stats work. Now we have a free downloadable version the SAS UE or the SAS University Edition operates with SAS version 9.4. Codes by Mike here still works actually with some slight syntax change. In 5:27 this syntax will will have a problem on SAS UE this will result to a Physical File is missing error for infile="C:\My SAS Files\main.txt. What worked for me is to follow the Unix syntax infile "/folders/myfolders/main.txt". The \folders is default in SAS UE and \myfolders is recommended user folder these are all needed to be setup with Oracle VM Virtual Box that actually uses Linux Red Hat on Windows 10 PRO on my laptop

  • Rene Santana
    Rene Santana Year ago

    Hey Mike I love your cheat sheet! Do you have this available to share? It's super useful!

  • ajay kushwaha
    ajay kushwaha Year ago

    By using libname also we can import file. So what is the major difference btw libname and proc import statement?

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  Year ago

      ajay kushwaha good question. Proc import is used for non SAS type data (i.e. .XLS, .csv, etc) and LIBNAME is for data already formatted in SASs format (.sas7bdat).

  • TheChocopollo
    TheChocopollo Year ago

    Hi, great video, thanks
    how can I import a .txt file with missing values??

  • Luke Aseffa
    Luke Aseffa Year ago

    Hi Mike, thanks for tutorials. One question, How can you proc import a zipped files that has pipe delimited and .excel extension?

  • TK
    TK Year ago

    Could you please share your template , any weblink for these templates...please advise

  • vivek koul
    vivek koul 2 years ago

    sir i am doing the same thing as you said but is still showing me error
    ERROR: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/F:\New folder\New folder\vivek.txt
    plz resolve this
    thanks

  • Asamoah Ishmael Ohene
    Asamoah Ishmael Ohene 2 years ago

    Please, share your cheat-sheet!

  • Cecil D. Blount
    Cecil D. Blount 2 years ago

    How to do import an SAS file into SPSS

  • SteveHovland
    SteveHovland 2 years ago

    Regarding the volume problem in the 2nd part: Your video editor should allow you to normalize the audio or at least adjust it up for the 2nd clip.

  • chi zhang
    chi zhang 3 years ago

    Hello, I am wondering how can I import all the excel files in a folder, instead of just import one specified excel file?Thank you so much,

  • Etienne Holder
    Etienne Holder 3 years ago

    Thanks for all the help! I had a question regarding importing two different excel sheets from the same excel file into SAS. I'm uncertain as to what the correct code is for this command. Thanks :)

  • tonton6969
    tonton6969 3 years ago

    hi,
    When I create my own librarie with the libname statement, the library disapears when I restart SAS. Is it normal? Is there a way to keep it?

  • varun kasal
    varun kasal 3 years ago

    I have a question for you. You used LRECL to specify length of the data being extracted. But even if we don't use LRECL, we can still import the data. So can you please tell the exact significance of LRECL and in which circumstances it is used?

    • varun kasal
      varun kasal 3 years ago

      Thanks a lot Mike

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  3 years ago +2

      If you don't use it, LRECL uses the default value of 250. If your dataset is larger than this, you're gonna have a bad time. Therefore, get in the habit of specifying it yourself everytime and you will consistently be performing quality performance checks to avoid downstream dilemmas.

  • Jessie Kull
    Jessie Kull 3 years ago

    Hi Mike,
    I am having trouble importing an excel data sheet. this is what i have written out:
    /* TEMPLATED CODE: .txt file type, with or without delimiters */
    data [appropriate data set name here];
    infile "C:\Users\jkull\Desktop.xls"
    LRECL=1000 DLM=' ';
    input
    Cow ID milking data fat protein scc
    ;
    run;
    and this is what the error message shows:
    ERROR 180-322: Statement is not valid or it is used out of proper order.
    53 Cow ID milking data fat protein scc
    54 ;
    55 run;

    can you help me with this?
    Thanks!
    Jessie

  • Venkatesan Ramamurthy
    Venkatesan Ramamurthy 3 years ago

    thanks for the lecture. do you have your template somewhere???

  • Anna Gsell
    Anna Gsell 3 years ago

    Would it be possible that you share your templates, so that I can read them and think about them / memorize them?

  • James R
    James R 3 years ago

    Mike, do us all a favor and stop jumping around from screen to screen!

  • James R
    James R 3 years ago

    Mike, you know allot on SAS. For feeble brains like mine who are struggling to get an assignment completed, it might be better and easier for us or at least me if you cut down the amount of info. I would make the video a little shorter. But good job and thanks.

  • Sunny Chadha
    Sunny Chadha 3 years ago

    U have imported .txt file by inline. so cant ke do the same to excel file as u are using proc import. Can u just brief whats the difference between using infile and import ?

  • Kushal Varaiya
    Kushal Varaiya 3 years ago

    Hi Mike,
    I am trying to import the excel file using the code:
    proc import
    datafile='C:\Users\DELL\Desktop\SAS programs practice\program1.xlsx' out=import_file
    dbms=excel replace;
    sheet='Sheet1';
    getnames=yes;
    run;
    BUT I am getting the following error in the log:
    ERROR: Invalid or missing file extension for this engine.
    ERROR: Error in the LIBNAME statement.
    ERROR: FILE= or TABLE= is required and must be specified.

    Could you please help.
    Thanks!

    • Kushal Varaiya
      Kushal Varaiya 3 years ago

      ok thanx!!

    • d4rcr3st
      d4rcr3st 3 years ago

      +Kushal Varaiya Open your Excel file, go to File and select Save As. Save the Excel file as an Excel 97-2003 Workbook (*.xls). Save the new Excel file on the same location and give it the same name. Next up adjust your datafile file name to:
      datafile="C:\Users\DELL\Desktop\SAS programs practice\program1.xls" out=import_file;
      (I changed the brackets from '...' to "..." and I changed the extension of your Excel file from ".xlsx" to ".xls".)
      Finally adjust your database to:
      dbms=xls replace;
      (I changed your database type from "excel" to "xls".)
      I believe you don't need to specify your worksheet, if there is no data on other sheets. Perhaps you can avoid the sheet command, to make the code less complicated to avoid any further errors. I think this should help you solve a few of those error, if not all of them.

  • Davide Pasqualini
    Davide Pasqualini 3 years ago

    Thank you

  • ronak shah
    ronak shah 3 years ago

    Can anyone please explain the use of FILENAME statement, preferably with an example? or even a link to some video will do!!

  • Pankaj Girhotra
    Pankaj Girhotra 3 years ago

    Thanks Mike for this nice video.
    Here is a problem which I faced. While importing Excel2010 file neither nor run.

  • Prafful singh
    Prafful singh 4 years ago

    Very helpful Tutorials. Thnk u :)

  • steeveepee33
    steeveepee33 4 years ago

    "Tab" delimiter is DLM='09'X

  • steeveepee33
    steeveepee33 4 years ago

    This is the first tutorial I've seen on here out of about 5 thus far that has actually mentioned DLM and LRECL. Thank you.

  • 2 llai
    2 llai 4 years ago

    Hi Mike
    Your video is very helpful.However, I have a problem when import my excel file. Can you help me?
    Here is my code:
    libname home '\\itfs1\amysore1\Desktop\SASUniversityEdition\myfolder\';
    proc import out =imported_excel
    datafile = '\\itfs1\amysore1\Desktop\SASUniversityEdition\myfolder\Train1.xlsx'
    dbms=excel replace;
    getnames=no;
    mixed=yes;
    RUN;
    Here is the error message:
    49 libname home '\\itfs1\amysore1\Desktop\SASUniversityEdition\myfolder\';
    NOTE: Libref HOME was successfully assigned as follows:
    Engine: V9
    Physical Name: \\itfs1\amysore1\Desktop\SASUniversityEdition\myfolder
    50 proc import out =imported_excel
    51 datafile = '\\itfs1\amysore1\Desktop\SASUniversityEdition\myfolder\Train1.xlsx'
    52 dbms=excel replace;
    53 getnames=no;
    54 mixed=yes;
    55 RUN;
    ERROR: Connect: Class not registered
    ERROR: Error in the LIBNAME statement.
    Connection Failed. See log for details.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.40 seconds
    cpu time 0.31 seconds

    • Toby Farms
      Toby Farms 3 years ago

      +Mike's SAS Tutorials
      How can I import a DAT file that does not have a header row??

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  4 years ago

      @2 llai Is your computer 32 or 64-bit? What about your SAS and Microsoft Office installations? One of them mismatches and hence you're getting that error. Try changing 'dbms=excel' to 'dbms=xlsx'. Otherwise, check out this link for more information: blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/

  • dampensson
    dampensson 4 years ago +3

    For those who, like me, are using the free university edition, you may encounter issues with the libname procedure. See support.sas.com/software/products/university-edition/faq/SAS_libname.htm for a solution.

  • Ramesh Kumar
    Ramesh Kumar 4 years ago

    hello Mike,
    I got a problem, using DATA infile process. I have got the variable name & variables are created but values have not entered it gave all dots in it

  • Deepa Jha
    Deepa Jha 4 years ago

    i have a problem done using two data steps, how could i do the same using just one data step?

  • Yami
    Yami 4 years ago

    Your tutorials are awesome! Thank you so much for sharing your knowledge.I've learned a lot even I just started to watch your video.. You're awesome!

  • Udoka Okpala
    Udoka Okpala 4 years ago +14

    Hi Mike. Love your videos. Its very short straight to the point. Please can you share your templated coded file- cheat sheet?

  • AllianceHalifax
    AllianceHalifax 5 years ago

    How do you read a data file in SAS studio?

  • wim1488
    wim1488 5 years ago

    Mike, thank you for the useful explanation.

  • TheGrasspond
    TheGrasspond 5 years ago

    This video series if awesome!!! A life saver for someone that needs to learn sas to get through a graduate stat course.

  • Glorietta Hurd
    Glorietta Hurd 5 years ago

    Mike not sure if you remember me from Loma Linda, but I just want to say that your videos are great. I appreciate how well you explain SAS. It's a great refresher and I hope you keep making videos when you can...all the best Mike! Also, going to buy your notes too!

  • Benoit Coupal
    Benoit Coupal 5 years ago

    Hi Mike.
    Very interested video, however, I got some problem with formating. Don't know if you could help.
    I have 50 csv files adding to 40MM lines (50 variables, but only 10 usefull). When I use your code, my dates changes (alternate YY and DD), some numeric data are truncated at the decimals (as integer)...
    How to properly set the different formats once at the beginning...
    And do you know some looping, so I won't do it 50 times ?
    Thanks (I'll donate, it's too usefull :-),
    Benoit
    PS: I'm not a programmer or big user of SAS (student)

  • stefan lazar
    stefan lazar 5 years ago +1

    Hi Mike, thank you very much for your tutorial.
    My name is Stefan, I'm student in France and I will start soon an internship where I will work in SAS and it is over an year that I did not use SAS and I'm afraid that I will have problems to remember all the code. I was wondering if you can borrow me your Templates Code. I know it's maybe innopropriete to ask that, but I would be very grateful if you could help me.
    Thank you in advance

  • Mohsen Article
    Mohsen Article 5 years ago

    Hi. I have an excel file including 4 sheets. First row of each sheet is the names of 100 companies (A1:CV1). Each sheet is actually a single time-series variable for 100 companies for 120 months. How to import them into SAS so that later on I could do analysis like regression?

  • Anirudh Samudrala
    Anirudh Samudrala 5 years ago

    So at 10:58 you are trying to create a dataset from an existing data set that is in Sas file type?

  • Anurag Shubham
    Anurag Shubham 5 years ago

    I am copying the same code for importing excel but always its shows this error
    ERROR: Connect: Class not registered
    ERROR: Error in the LIBNAME statement.
    Can you please help me out

  • Just Me
    Just Me 5 years ago

    what is the need of "out" after "import"?
    Is there any place from where I can get such templated codes for various proc statements?
    if I aldready had a file named "imported_excel "at the same destination then will the proc import overwrite it or give an error?

  • Christian Hand-Bourgin
    Christian Hand-Bourgin 5 years ago +1

    I cant find the template of the codes you showed below the youtube video. I'm sorry for such a stupid question

  • Fafan Chwan
    Fafan Chwan 5 years ago +1

    thanks Mike for this great job , but please can you add the french subtitle cause i can't follow your explanation in english :) thank you !

  • C C
    C C 5 years ago

    Hi, thanks for all your help so far. I have a problem with importing a excel file however. I keep getting this error message when I use your code
    ERROR: Connect: Class not registered
    ERROR: Error in the LIBNAME statement.
    Do you know anything about it?? Thanks.
    edit: haha just joking i know how to do it

  • Learning
    Learning 5 years ago

    Great vids man. New SAS user here so this is very helpful. My knowledge base is more SPSS and trying to get a handle of SAS. Trying to import an spss file into sas w/o losing the variable names and values. Any sugggestions?

  • Muzammil Shah
    Muzammil Shah 5 years ago

    can we import an Excel file by the ''Infile" statement???
    I am able to import a txt file. Also i can import Excel file through the wizard but can we import Excel files through Import Statement??

    • Muzammil Shah
      Muzammil Shah 5 years ago

      thanks

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  5 years ago +2

      Excel files are imported using proc import as in the example. You might have to try different versions of the "DBMS=''" option perhaps "excel" or "xls" or even "xlsx" depending on the filetype. Infile won't work because it is used in handling text data and I believe excel data isn't stored internally in a format that infile would recognize and be able to read.

  • MOA518
    MOA518 5 years ago

    Awesome, Mike! Thanks.

  • AlbusPHolmes
    AlbusPHolmes 5 years ago

    Hi, thanks a lot for this tutorial. I have a problem. In my Libraries folder, I didn't have a library named Home, so I simply made one (right-click -- New -- Home). However whenever I try to run the command
    data sas_format; set home.main;
    run;
    I get an error statement:
    data sas_format;
    ----
    22
    ERROR: Libname HOME is not assigned.
    ERROR: Error in the LIBNAME statement.
    51 set home.main;
    ---
    180
    ERROR 22-7: Invalid option name DATA.
    ERROR 180-322: Statement is not valid or it is used out of proper order.
    Any help? Thanks!

    • AlbusPHolmes
      AlbusPHolmes 5 years ago

      You've been a lot of help. Thanks!

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  5 years ago

      Depends how familiar you are with statistics in general. SAS programming is a combination of computer programming know how with statistical knowledge. If you're familiar with most statistics, you can be doing common statistics regularly at the end of my series and practice/time; I'd say a month of solid practice to get comfortable enough with it all. Advanced techniques are just the same programming plus the advanced statistics. Overall, you could pass the BASE SAS exam after a month or so, in my opinion. That'll be it for my responses for now though, best wishes on your success! Hope I've helped some.

    • AlbusPHolmes
      AlbusPHolmes 5 years ago

      You rule with quick replies :). Yes I would fall into the H1B category applicant, and I'm just starting out with learning SAS. I have a bit of base SQL experience from a college class I took, and I'm studying SAS mainly from your videos (I'll be glad if you can recommend other resources) but not the other languages. How long do you think it takes to gain SAS proficiency if I'm diligent enough with studying? Thanks a great deal for yourhelp thus far.

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  5 years ago

      Depends on the industry you work in. Lots of people look for SAS proficiency, but you also need to know R, STATA, SPSS, S-PLUS, SQL, and other languages to be really competitive. Expect lots of job competition from H1B applicants and salaries to go down over time. I would suggest getting your feet wet in the industry you want, then trying to move into leadership ASAP so you stay competitive for managerial and supervisory functions - which usually have lower rates of H1B competition.

    • AlbusPHolmes
      AlbusPHolmes 5 years ago

      Thanks for the prompt reply! I did reference the Libname statement to HOME, but it turns out everything was still going to the WORK library instead. Another quick question though - how easy is it to get a job with SAS proficiency?

  • nidhesh20
    nidhesh20 5 years ago

    when i read to read a text file, its giving me a error message i.e.
    Invalid data for name in line 1 1-8
    Invalid data for name in line 1 10-16
    My data in txt file is :
    Cucumber 50104-A 55 30 195
    i think variable type is num instead of char... so how can i change the variable type.. and what measures i should take care while using alphanumeric variables.. thankx

  • Justin K
    Justin K 6 years ago

    how can I get the sas interface program to run all of this

  • Jimmy Writes
    Jimmy Writes 6 years ago

    Hi All, Will Mr. Mike pl advice me (1) What should be the best approach for an illeterate guy like me to learn SAS 2 claim that I have workig knowledge of SAS.will it help 2 purchase a text book 2 learn SAS? should I get enrolled local college (3) What R the job prospect (one of my coworker got a job as a Reporting Coordinator Lvl II & he was inquired about his SAS competencies in the interview). I got undergrade degree in business (Fin, Acct etc) Your guidance is much appreciated. Thanks. JJ

  • Tanmay Patil
    Tanmay Patil 6 years ago

    thanks !