Excel to SAS date format issues

[fordcr2]

Hello, 

I know there are a lot of posts about formatting dates, but I have not been able to find one that works for my problem. I am trying to import data where the dates are formatted like "2/21/2019" from an xslx workbook with multiple sheets. When I bring these sheets into SAS, the dates are converted to  "43517" and this is being considered a character variable by sas. I have tried to make a new date variable using:

date_=input(Date, mmddyy10.); 

but I keep getting this error: 

NOTE: Invalid argument to function INPUT at line 579 column 34

Please help! 

Thank you

[Tom · Accepted solution]

One or more of the values in the column in Excel are NOT in date format.  So SAS has converted the whole column to character to handle the mixed data types.  This causes the actual dates to appear in SAS as a character string representing the number that Excel uses for that date. 

Convert it to a number and adjust the number for the difference in base date used by Excel and SAS.  SAS stores dates from 1960 and Excel uses 1900 as the basis.  But SAS numbers from zero and Excel from one and Excel thinks that 1900 was a leap year.  So use 30DEC1899 (which will be a negative number in SAS's numbering scheme) as the offset.  Attache a format so that humans can read the values when they print.  (Don't use mmddyy or ddmmyy format because either one will confuse half of your audience. )

real_date = input(Date, 5.) + '30DEC1899'd ;
format real_date yymmdd10. ;

[fordcr2]

That worked great! Thank you. I just needed it in the 02/21/2019 format to use for an internal study. 

[Mohan2020]

Hello,

I have similar issue while importing .xls to SAS. I used the proc import and created SAS dataset. The time filed has a value like '11:05'  in .XLS sheet , after i imported it to SAS dataset , it displays the value of this 'time' field as '0.4618056' , all values for 'time' populated as decimal number in SAS dataset, Pl. let me know how i can fix it and displays the correct value in 'time' field in SAS dataset. Appreciate your help.

[Tom]

---

@Mohan2020 wrote:

Hello,

I have similar issue while importing .xls to SAS. I used the proc import and created SAS dataset. The time filed has a value like '11:05'  in .XLS sheet , after i imported it to SAS dataset , it displays the value of this 'time' field as '0.4618056' , all values for 'time' populated as decimal number in SAS dataset, Pl. let me know how i can fix it and displays the correct value in 'time' field in SAS dataset. Appreciate your help.

---

Fix the XLS file if you can so that the column only has one type. Either strings or TIME values.

If you cannot then use the same trick as the DATE field. First convert the string into a number and then adjust it to be a valid TIME value.  It looks like that you have the fraction of a day, so multiply that number times the number of seconds in a day.

time_num = '24:00't * input(time,32.);
format time_num time5.;

[Mohan2020]

Thanks a ton for your help. it worked. 

i just found one more issue, while converting from .xls to sas , i found  barcode has values like ‘6.50E+11 ‘ for few records in excel spreadsheet and when It was converted to SAS dataset , the barcode value  has leading zeros  like '65234300000' , pl let me know how i can fix it.
 

[Tom]

---

@Mohan2020 wrote:

Thanks a ton for your help. it worked. 

i just found one more issue, while converting from .xls to sas , i found  barcode has values like ‘6.50E+11 ‘ for few records in excel spreadsheet and when It was converted to SAS dataset , the barcode value  has leading zeros  like '65234300000' , pl let me know how i can fix it.
 

---

You need to say a lot more about what you actually have to get help.  You mentioned leading zeros but don't show any values with leading zeros. You don't explain whether the variable is numeric or character in the SAS dataset. You don't explain whether all of the cells in the column in XLS are numeric or not.  SAS and Excel will both default to displaying large numbers using the scientific notation you mentioned.  The way a number is displayed does not change the way it is stored.

That might also be related to have a trashed XLS file.  For example if you use Excel to open a text file (like a CSV file) it will by default guess how to interpret each value.  If it sees a value that looks like a number it will make it into a number.  So leading zeros will disappear as they have no meaning in a number.  Or it might think that a string with / or - in it is a date instead of a string.

To have more control when reading the data into SAS it might help to have Excel write a CSV file and then read that file using a data step.  But if the data is already corrupted in the XLS file (either by Excel's import issue or by user error) then that might not solve all of the problems.

[ballardw]

I am going to guess that you are using proc import. You may want to run Proc contents on each of the data sets generated from the different sheets, especially if they are supposed to have the same layout. Proc Import by default, and requires registry setting changes to modify, only uses the first 20 rows of a sheet to set variable properties. So unless the longest value of character variable appears in those first rows values may be truncated. Since each sheet is treated separately the lengths of variables may change as well as variable type from sheet to sheet.

You may have better luck in the long term by:

1) save all the sheets to CSV files

2) use proc import to read one of the files. Use a large value for the guessingrows parameter to examine more lines of the file before setting variable properties. This will generate data step code to read the csv that appears in the log.

3) copy the data step code from the log to the editor (save) and make sure all of the informat statements set the desired length and type. Set lengths for character variables at least as long as the longest expected value. Set correct informats for the dates.

4) Test the code.

5) Change the infile and data set names for each work sheet CSV file (or find the messages on the forum that address reading multiple files at one pass with a data step).

The CSV generated may have quotes around the dates that were entered as character. The above data step can read them as such just fine. Example:

data example;
   informat x date9.;
   format x date9.;
   input x;
datalines;
01JAN2019
"02Jan2019"
3Jan2019
;
run;