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

  • colton donathan
    colton donathan 4 months ago +1

    this guy is a baller!

  • MasterofPlay7
    MasterofPlay7 5 months ago

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

    • MasterofPlay7
      MasterofPlay7 5 months ago +1

      thx bro, much appreciated!

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  5 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 5 months ago

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

    • MasterofPlay7
      MasterofPlay7 5 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  5 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 5 months ago

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

    • MasterofPlay7
      MasterofPlay7 5 months ago

      thx bro!

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  5 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 5 months ago

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

    • MasterofPlay7
      MasterofPlay7 5 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  5 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 6 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  6 months ago

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

    • Claude Daiga
      Claude Daiga 6 months ago

      Thanks very much Mike.

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

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

  • Berns Buenaobra
    Berns Buenaobra 10 months 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

  • Raja Singh
    Raja Singh Year ago

    Is there no difference between libname statement and set statement? Both are used for reading external data?

  • vivek koul
    vivek koul Year 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 2 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 2 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 2 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 2 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 2 years ago

      Thanks a lot Mike

    • Mike's SAS Tutorials
      Mike's SAS Tutorials  2 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 3 years ago

    Very helpful Tutorials. Thnk u :)

  • steeveepee33
    steeveepee33 3 years ago

    "Tab" delimiter is DLM='09'X

  • steeveepee33
    steeveepee33 3 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 3 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  3 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?

    • Raja Singh
      Raja Singh Year ago

      Is there no difference between libname statement and set statement? Both are used for reading external data?

  • AllianceHalifax
    AllianceHalifax 4 years ago

    How do you read a data file in SAS studio?

  • wim1488
    wim1488 4 years ago

    Mike, thank you for the useful explanation.

  • TheGrasspond
    TheGrasspond 4 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 4 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 4 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 !

  • calvin choi
    calvin choi 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 5 years ago

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

  • Jimmy Writes
    Jimmy Writes 5 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 5 years ago

    thanks !

  • anewmachine00
    anewmachine00 5 years ago

    This is just GREAT teaching!!

  • Michael Kristensen
    Michael Kristensen 5 years ago

    Maybe the problem is, that your excel sheet is .xlsx format, not xls.
    Try using dbms=xlsx. Note that not all option are possible as with dbms=excel, so you can't use the sheet='sheet1'.
    I hope it will help you.

  • Moeamen Abbas
    Moeamen Abbas 5 years ago

    Please Do one of you help me, I have a problem How do I print the results in the program, is unable to print and save?

  • dfelipe32
    dfelipe32 5 years ago

    I have an error:
    114 datafile="C:\Users\Felipe G-Macboo Pro\Documents\Linear Models\hw1\importarsas.xlsx"
    115 dbms=excel replace;
    116 *Optional statements are below; sheet='Sheet3';
    117 getnames=yes;
    118 run;
    ERROR: Connect: Clase no registrada
    ERROR: Error in the LIBNAME statement.
    Fallo en la conexión. Consulte la log para obtener más información.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDIMIENTO IMPORT used (Total process time):

  • fallen4fate
    fallen4fate 5 years ago

    Hi. These tutorials are very good. I am having trouble getting my file recognized. It says ERROR: Physical File does not exist also like other people in the past. I've tried everything and looked it up multiple places but I'm not sure how to fix it. The file does exist.
    data infile_main;
    infile "C:\Users\Public\main.txt" (I also tried putting it on my Desktop and various other locations)
    LRECL=100 DLM=" " firstobs=2;
    Input
    x y z
    ;
    Run; Thanks

  • Ying Huang
    Ying Huang 5 years ago

    Hi Mike,
    I like your template code file so much. How I can get it? You mentioned we can download it, but I didn't find it. Could you please send me the link?
    Thanks,
    Kelly

  • Faraz70
    Faraz70 5 years ago

    when i try to read a text file, it gives me the error
    ERROR: Physical file does not exist, /sso/biconfig/Lev1/SASApp/C:\D­ocuments and Settings\SAS\testt.txt.
    the file is there, checked its name and path..but still gives me error
    any suggestions...Thanks

  • trichman22
    trichman22 5 years ago

    Awesome, thanks so much! any chance you might share the template you've created?

  • Shaoshan Liao
    Shaoshan Liao 6 years ago

    Hi Mike, thank you very much for your tutorial. You teach in a very clear and concise way. However, when I run my codes,
    libname home "e:\SAS\";
    DATA sas_format;set home.main;
    run;
    data home.sas_format;set work.infile_main;
    run;
    proc import out=imported_excel
    datafile='E:\SAS\main.xls' dbms=excel replace;
    sheet='Sheet1';
    getnames=yes;
    run;
    and the log says
    ERROR: Connect: Class not registered
    ERROR: Error in the LIBNAME statement.

    Could you please help me with the codes above? thanks

  • Venki Ramaiyan
    Venki Ramaiyan 6 years ago

    Hello Sir, First of all thanks a lot to teach what is SAS. I am a beginner. I would like to purchase your cheat sheet. can we use this sheet whenever we need codes on the work place to type anything in clinical sas jobs? So this is replace the google search rite. Please clear my doubt. i want to purchase this.

  • camvamsi
    camvamsi 6 years ago

    Thanks it worked

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

    Take out the semi-colon after the datafile="" part. You're cutting off your code too soon. That line needs to include the "dbms=" and "replace" keywords.

  • camvamsi
    camvamsi 6 years ago

    I have used the following statements, but still getting the same error, what could possibly go wrong
    Proc Import out=GardenNew;
    datafile="C:\Documents and Settings\vamsi\Desktop\Garden.xls";
    dbms=excel replace;
    sheet='Sheet1';
    getnames=yes;
    run;

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

    You just need to specify the file location. If you see the comments below the video, the line "datafile=''" is what you are probably missing. Datafile and file are both keywords that tell SAS where your excel file is located on your computer.

  • camvamsi
    camvamsi 6 years ago

    Hi,
    i am trying to import an excel sheet, but every time i am getting the following error, i have no idea how to solve this.
    Error : File= or Table= is required or must be specified
    what should i do.

  • Zxcvb488
    Zxcvb488 6 years ago

    i wanna say thank you million times :D

  • tomite2001
    tomite2001 6 years ago

    Yes I got the link now. Thanks

  • tomite2001
    tomite2001 6 years ago

    Hi. How can i get a copy of your 'cheat sheet'. It looks like a very good reference guide

  • Justin McGinnis
    Justin McGinnis 6 years ago

    Your videos are super helpful! Especially your personal insight, like the Template Code. Would you be willing to share your Template Code file?

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

    Try changing the "DBMS=excel" to "DBMS=xls" or "DBMS=xlsx" depending on your file type. If you are unsure of the filetype, go to your excel file, right click on it and go to the properties. Under the file name, you should see the filetype extension at the end of the file name.

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

    To those getting this error, try changing the "DBMS=excel" to "DBMS=xls" or "DBMS=xlsx" depending on your file type. If you are unsure of the filetype, go to your excel file, right click on it and go to the properties. Under the file name, you should see the filetype extension at the end of the file name.

  • Ning Huang
    Ning Huang 6 years ago

    Me too! I can't figure out why. Did you?

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

    After the input statement variable name, add a dollar sign $. :)

  • William Henry
    William Henry 6 years ago

    Mbate001: Thanks..good explanation and demos!

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

    Excellent point. I tend to exaggerate numbers when I'm teaching a lesson but yours is definitely the accurate answer. If I recall correctly, if you specify a number greater than 32k, SAS automatically corrects it and notes it in the log.

  • Miguel Mizraim Serrano Morales

    HI MY TESIS WORK IS ABOUT BIOLOGICAL CONTROL OF PEST AND I HAVE TO DO AN TURKEY AND ANOVA TEST, SO COUL YOU PLEASE GIVEME AN EXAMPLE OR DE CODE TO DO THIS IN SAS?

  • Nitin Binjola
    Nitin Binjola 6 years ago

    could You Please share Template File for all code.
    My email ID is nitinbinjola1202@gmail.com

  • Nerissa Martínez
    Nerissa Martínez 6 years ago

    Hi mbate001 - I realize that I need to learn more of SAS and SQL for financial reporting and I will apreciate if you can send me your cheat sheet directly to me. Your video are awesome, but I want to have the cheat sheet to make reference when I am using in my future job. nveilleux09@yahoo.com Thanks

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

    The code shown in the video is always in the description of the video. If you want the cheat sheet, I've included the link in the description as well.

  • Peifeng Hu
    Peifeng Hu 6 years ago

    Could you please share the codes document in here?

  • Juve
    Juve 6 years ago

    Mike, excellent Tutorial, again, I have watched two videos so far. I am new to SAS. are you going to be showing how to templatize in other videso? I am not sure how to do that :)

  • quiznitch
    quiznitch 6 years ago

    i had the same prob - for me i was using 2010 excel xlsx files, when i saved as xls 2003 type it worked!

  • Ram Ghosh
    Ram Ghosh 6 years ago

    Great video, I was successful until I started importing an excel file. I keep on getting following message:
    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.04 seconds
    cpu time 0.04 seconds

  • Kristi Dubya
    Kristi Dubya 6 years ago

    This video was so helpful. Do you know how to INFILE a dataset if you don't know the column information? I'm trying to view the data in SAS but don't know what to type in as my INPUT statement. Can you help?

  • Norfeldt
    Norfeldt 6 years ago

    Well, thank you for these videos :D

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

    @Norfeidt, Hooooollly shit! Thank you for this pointer! Seriously what an awesome feature I never knew about!

  • Norfeldt
    Norfeldt 6 years ago

    @mbate001 to quickly get the location of the file with extension:
    SHIFT + Right click / Copy as path
    A big time-saver...

  • Diana Streng
    Diana Streng 6 years ago

    Good work mike. Two thumbs up.

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

    The SAS syntax is provided in the comments below the video.

  • ZuckerMan
    ZuckerMan 7 years ago

    Thank you Mike a lot, please send me the tutorials with SAS syntax ".docx" on my e-mail slouami@hotmail.fr ... Best Regards.