Importing date files from excel (xlsx) to sas

[Anita_n]

Hello all, 

there are some set of data stressing me up. please help.

Am tring to import a dataset into sas with date fields. In excel the date field is formated as date (04.06.2020)

but when this is imported to sas it changes to character (2020-06-04). I have tried all possible means but it isn't working. 

hier test data :

in excel (formatted as date)        in sas (changes to character)

04.06.2020                                    2020-06-04

05.07.2020                                    2020-07-05

22.10.2019                                    2019-10-22

sas code1: 
proc import datafile= "mypath\myindat.xlsx" out= myoutdat 
dbms=xlsx replace;
run;


sas code2:

data patient;
%let _EFIERR_ = 0; 
infile "mypath\myindat.csv" delimiter = ';' MISSOVER DSD lrecl=13106 firstobs=1 ;
informat mydatvar mmddyy10.; format mydatvar mmddyy10.;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

I will ne very grateful for any help

[Tom]

You need to provide more details or examples.

Do you have actual EXCEL files as in your first SAS code fragment? Or just a text file as in your second SAS code fragment?

If you are reading from an XLSX file then SAS will not convert dates in the way you show.  It will either read the dates properly or if the column in the XLSX file has mixed numeric and character values it might convert the dates into character strings that represent the number that EXCEL uses to store that date.  It the cells in the XLSX file are strings and not dates then their values will not change.

If you are reading from a text file then just write your own data step to read the file and you can have complete control over how the dates are read and what format is attached to them to make them display in a human recognizable way.

[Anita_n]

actually am reading from an xlsx datafile so the proc import is the one I used. Since it wasn't working properly, I converted the xlsx data to csv , but it didn't work either

I don't really understand why sas converts the date fields to character. I checked the field there are only dates in there and they aren't mixed with characters.

[Tom]

Run PROC CONTENTS on the SAS dataset created by the PROC IMPORT.  Are you sure you don't have a numeric variable that is being displayed with the YYMMDD10. format?

[Anita_n]

proc contents says the field is a character field

2	Datum_FAX	Char	10	$10.	$10.	Datum_FAX

[Tom]

So the column in the XLSX file is not numeric.  There is at least one cell in that column that does not have a number (both Excel and SAS store dates as number of days, they just use a different base date for counting).

[Anita_n]

okay, I will try to search. Thanks a lot

[Anita_n]

Hi, I was able to import some of the columns from excel to sas in the right format. I read a post which says for 32 bits sas dbms should be equal to excel so I changed my code to: 

proc import datafile= "mypath\excelfile.xlsx" out= myoutfile
dbms=excel replace;
	GETNAMES=YES;
	MIXED=YES;
	SCANTEXT=YES;
	USEDATE=YES;
	SCANTIME=YES;
run;

and it worked worked for some columns but for one particular column not.

I will like to mention that this column is a calculated column in excel. In sas this column 

is imported as a string. In excel the date is displayed as  16.11.2018 (date format). In sas it's displayed as  16.11.2018 (string $10.) I tried to use this code to convert the string back to date but it isn't working:

data newfile;
set oldfile;
format  stringdate ddmmyy10.;
run;


ERROR 48-59: Format $DDMMYY was not found or could not be loaded

please any help?

[SASKiwi]

You can't apply a numeric date format to a character date string. You first need to convert the date string into a new numeric variable. Try using the INPUT function to do the conversion:

data newfile;
set oldfile;
numericdate = input(stringdate, ddmmyy10.);
format  numericdate ddmmyy10.;
run;

[Tom]

So it seems the formula is generating a string instead of a date value.  You can use the INPUT() function to convert the string to a date value.  But since the current variable is character you will need to make a new numeric variable.  Once you have a date value you can attach any date format.  Personally I prefer to use either DATE or YYMMDD format for dates to avoid the confusion that using D-M-Y order or M-D-Y order can generate.

realdate= input(stringdate,ddmmyy10.);
format realdate date9.;

If you want to leave the variable as a string you could use INPUT() and PUT() together to generate a new string with a different way of presenting the date value.

stringdate=put(input(stringdate,ddmmyy10.),yymmdd10.);

But note you might then lose any other type of data that might be in that character string.  Such as partial date indications like 'xx.01.2020'.

[Anita_n]

Thanks to you for the tipps, unfornately it's still not working. I have decided to leave the issue till I get back to work after the holidays. The file is given me headache.  I will let you know if am able to fishout the cause of the problem. Thanks a lot.  

[SASKiwi]

I also suggest you try saving your Excel worksheet as a CSV file as that will give you much better control over data types and formats.