Import data from Excel including date format

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 103
Accepted Solution

Import data from Excel including date format

[ Edited ]

Good morning my friends:

 

I have studied lots of tutorials talking about how to import correctly EXCEL files into SAS UNIERSITY EDITION, unfortunely all the trials were unsuccesfull, here i1ll show you thie tabe i have.

 

ID ID_BIRTH_DATE AGE SEX
81 07/02/2007 . M
271 08/26/2007 . M
739 . . M
1515 12/01/2007 . M
1533 . . M
3041 . . F
3122 08/29/1997 . M
3123 08/29/1997 . F
3124 09/08/1997 . F
3125 09/08/1997 . M
3126 09/08/1997 . M
3127 10/12/1997 . M
3128 10/12/1997 . F
3129 11/18/1997 150 F
3130 11/18/1997 149 M
3131 11/19/1997 149 M
3131.1 11/19/1997 . M
3132 11/19/1997 . F
3133 11/19/1997 151 F
3134 11/19/1997 151

M

 

importing the file i use this commands:

 

PROC IMPORT DATAFILE="/folders/myfolders/EXAMPLE.xls"
    OUT=work.TEST1
    DBMS=XLS
    replace;
RUN;

 

and i obtain this:

 

ANIMAL ANIMAL_BIRTH_DATE PREGNANCY_LENGTH_DAYS SEX
81 39265 . M
271 39320 . M
739 . . M
1515 39417 . M
1533 . . M
3041 . . F
3122 35671 . M
3123 35671 . F
3124 35681 . F
3125 35681 . M
3126 35681 . M
3127 35715 . M
3128 35715 . F
3129 35752 150 F
3130 35752 149 M
3131 35753 149 M
3131.1 35753 . M
3132 35753 . F
3133 35753 151 F
3134 35753 151 M

 

i  NEED SOME ADVICES TO IMPORT CORRECTLY THE FORMAT DATE TO SAS UNIVERSITY EDITION.

 

i1M SENDING THE ORIGINAL XLS FILE TOO

 

THANK YOU


Accepted Solutions
Solution
‎03-31-2016 12:32 PM
Super User
Posts: 18,997

Re: Import data from Excel including date format

You can try, but there's too many factors in play. 

 

It depends on the version of Excel used, mac vs windows have different issues, your SAS version and the bitness between the two.

 

The sad fact is that proc import guesses...and its guesses aren't worth betting on. 

 

Excel is not a database and does not have fixed types that it enforces for cells. This is create for excel, but not for data management.

 

View solution in original post


All Replies
Super User
Posts: 7,381

Re: Import data from Excel including date format

You obviously get the original, Excel-internal date value.

Do

format ANIMAL_BIRTH_DATE mmddyy10.;
ANIMAL_BIRTH_DATE = ANIMAL_BIRTH_DATE - ('01jan1960'd - '30dec1899'd);

This should correct all your problems.

Alternatively, export your data from Excel to a reasonable transfer format like CSV, and import the data into SAS from that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 103

Re: Import data from Excel including date format

kURT:

tHANK YOU FOR YOUR ANSWER, BUT I WOULD LIKE TO UNDESRTAND WHY I CAN'T GET THE SAME RESULTS AS SHOWS THIS YOUTUBE TUTORIAL (SAS MADE) (https://www.youtube.com/watch?v=oWBOEy9wSjo) IN THE MINUT 3:41.

The objective for using automatic importing is to save time formating after make the importation work. Could you helpo me to undertand this??

Thanks you very much
Respected Advisor
Posts: 3,786

Re: Import data from Excel including date format

[ Edited ]

The video is using XLSX not XLS as you have.

 

EDIT: my testing show that for this data just changing to XLSX does not fix it.

Frequent Contributor
Posts: 103

Re: Import data from Excel including date format

i tried both, and not same result Smiley Sad
Respected Advisor
Posts: 3,786

Re: Import data from Excel including date format

Yea me too.

 


jonatan_velarde wrote:
i tried both, and not same result Smiley Sad

 

Frequent Contributor
Posts: 103

Re: Import data from Excel including date format

is there any document to use in this case?? or contact to SAS online suport
Solution
‎03-31-2016 12:32 PM
Super User
Posts: 18,997

Re: Import data from Excel including date format

You can try, but there's too many factors in play. 

 

It depends on the version of Excel used, mac vs windows have different issues, your SAS version and the bitness between the two.

 

The sad fact is that proc import guesses...and its guesses aren't worth betting on. 

 

Excel is not a database and does not have fixed types that it enforces for cells. This is create for excel, but not for data management.

 

Frequent Contributor
Posts: 103

Re: Import data from Excel including date format

I exactly what i was expenting, currently i'm ussing LIBRE OFFICE to generate the EXCEL FILE, so maybo this is the problem.....i'll continue searching and/or trying into this, if anythig goes good here i'll let you know, thak you very much,
Super User
Posts: 11,104

Re: Import data from Excel including date format

[ Edited ]

Use LIbre Office to make a CSV file and import that. You'll likely have better luck. Also you can use the GuessingRows = statement to use more than the 20 rows or so that is used to "guess" when reading Excel files.

It may help to force your date columns to use the same date appearance before exporting to CSV.

 

An additional advantage of importing CSV is that SAS generates datastep code that should appear in the log. You can see exactly what was assigned for informat, format and variable name. And if you don't like something you can copy the code to the editor and modify it as needed.

 

If you are going to process many files of the same format I highly recommend getting the data step code. Then you can read other files and variable types stay the same, lengths of character variables stay the same and you can add labels and possibly even some initial data checking or custom error or warning messages for out of range or missing values that shouldn't be.

Frequent Contributor
Posts: 103

Re: Import data from Excel including date format

Baller, i appreciate a lot your contribution, could you help me to learn this mwthodology please??? if you could send me some information to delsolarvelarde@gmail.com, or an example i will reproduce it.

Thank you very much
Super User
Posts: 11,104

Re: Import data from Excel including date format


jonatan_velarde wrote:
Baller, i appreciate a lot your contribution, could you help me to learn this mwthodology please??? if you could send me some information to delsolarvelarde@gmail.com, or an example i will reproduce it.

Thank you very much

Basic steps are very easy:

1) Find or create a basic CSV file.

2) Either use Proc Import code or the SAS file data import wizard for a CSV delimited file.

3) Look in the log.

There should be program with an infile statement describing the options used, Informat and Format statements, an Input statement and two lines related to checking if you have critical errors.

 

The Informats will tell you how SAS read a specific variable. If you thought it shoud be numeric and is character then you either have the Guessingrows value too small or something other than numeric values in the data.

If I want to consider longer character values, a common item, consider what is read. Your example data may have the longest value for a First Name to be 7 characters but you know that isn't likely to be long enough in other files, so increase the Informat and Format to a larger value such as $15. (I typically use $20 for first and middle names and $30 for last but your data may differ).

 

If a variable you thought was a date but gets read as a simple number (likely best32. or similar) look at your values. A "date" of 20140728 doesn't trigger the "guessing" rules of SAS to think of it as a date that a value like 07/28/2014 would. But you could change the Informat to YYMMDD8. to read as a date (and assign a date format for that variable that you like).

This is especially critical if you use "dates" with 2 digit years especiall if not at the end. For instance what date is 01/03/05 ? This could be Month first, Day of month first, or year first depending on data source.

 

The details are related to INPUT, INFILE, INFORMAT statements predominately.

Super User
Posts: 7,381

Re: Import data from Excel including date format


jonatan_velarde wrote:
kURT:

tHANK YOU FOR YOUR ANSWER, BUT I WOULD LIKE TO UNDESRTAND WHY I CAN'T GET THE SAME RESULTS AS SHOWS THIS YOUTUBE TUTORIAL (SAS MADE) (https://www.youtube.com/watch?v=oWBOEy9wSjo) IN THE MINUT 3:41.

The objective for using automatic importing is to save time formating after make the importation work. Could you helpo me to undertand this??

Thanks you very much

Excel is very locale-dependent, and the (MS-supplied, therefore unreliable!) interface module (Data Access Objects) often delivers data in a way that makes it hard for SAS to realize that a column has certain attributes, like a date format.

 

The ONLY (in my dire experience) reliable way to communicate with Excel is to use a text-based file format (so you can inspect it with a simple text editor) and force correct formats in every step of the data transfer.

 

PROC IMPORT relies on guessing, and is good to do a "first shot", but it is in NO WAY meant for production use.

 

"Automatic importing" means setting up a reliable chain of steps which can be run from a scheduler and where every step checks for correct data and exits with error conditions if the criteria are not met.

 

Always keep in mind: computers are dumb, and making them do clever things is YOUR job.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1698 views
  • 1 like
  • 5 in conversation