topic Re: Import a variable having different date format from xlsx file in SAS Programming

Import a variable having different date format from xlsx file

Himanshu4 — Wed, 08 Feb 2023 20:28:56 GMT

i have a variable in xlsx file having different format for date variable and need to upload it in SAS and create a dataset with date format. any suggestions??

Re: Import a variable having different date format from xlsx file

PaigeMiller — Wed, 08 Feb 2023 20:55:29 GMT

Are these stored in a character variable as text strings after you import into SAS? (Please don't tell me what they are in Excel, as that is irrelevant)

If they are text, you can possibly convert them into valid dates using the ANYDTDTE. informat which guesses what the format is and can (I think) handle different formatting on different rows.

Re: Import a variable having different date format from xlsx file

Himanshu4 — Wed, 08 Feb 2023 23:35:34 GMT

After uploading it in excel its character, however data is populating like below, means. There is no specific format. And i would need that in a date format.
18Jan2020
45677

Re: Import a variable having different date format from xlsx file

Sajid01 — Thu, 09 Feb 2023 00:04:34 GMT

Hello @Himanshu4
Please try importing first and let us know if you are facing error.

Re: Import a variable having different date format from xlsx file

SASKiwi — Thu, 09 Feb 2023 00:20:59 GMT

Try changing the column type in Excel from General (the default) to Short Date then reimport.

Re: Import a variable having different date format from xlsx file

Tom — Thu, 09 Feb 2023 04:40:31 GMT

@Himanshu4 wrote:
After uploading it in excel its character, however data is populating like below, means. There is no specific format. And i would need that in a date format.
18Jan2020
45677

If you see both of those values in the same variable in the same dataset on different observations then your column in Excel has at least one cell that has a string in it instead of a date. In this case it is the the string '18Jan2020'. The other string '45677' is how SAS pulls the number that Excel uses to store the date 20JAN2025 (if it is using 1900 as the base date) or 20JAN2029 (if it is using 1904 as the base date).

2876  data _null_;
2877  excel=45677;
2878  sas=excel+'30dec1899'd ;
2879  put excel=comma7. sas=comma7. ' -> ' sas date9.;
2880  sas=excel+'31dec1903'd ;
2881  put excel=comma7. sas=comma7. ' -> ' sas date9.;
2882
2883  run;

excel=45,677 sas=23,761  -> 20JAN2025
excel=45,677 sas=25,222  -> 20JAN2029

To convert both those strings into actual date values you will need to do some testing to see which you have.

So you might assume that most of them were value Excel Date values and so first try to convert the 5 digit strings into dates and then when that did not work try to convert the string using the ANYDTDTE informat.

So if the dataset you created from the Excel sheet is named HAVE and the column that was supposed to have date values is named DATESTRING then here is data step that will make a new numeric variable named DATENUM that has actual date values and has a format attached that will print the date values in a way that humans can understand.

data want;
  set have;
  datenum = input(datestring,??32.)+'30dec1899'd;
  if missing(datenum) then datenum=input(datestring,anydtdte20.);
  format datenum date9.;
run;

Re: Import a variable having different date format from xlsx file

Kurt_Bremser — Thu, 09 Feb 2023 06:52:33 GMT

Assign a consistent date format to the whole column in Excel. Then save the spreadsheet as a csv file and read that with a data step.