import excel file into SAS

[Alexxxxxxx]

Dear all,

I have the excel file looks like this

#NAME?	BDATE
77605Q	31/08/2011
15455F	03/02/2003
286279	08/03/2000
31192X	17/02/2006
130165	11/12/1990
130298	22/02/1994
130412	03/03/1994
13052M	16/01/2001

When I import this excel file into SAS by the following code,

proc import datafile = "G:\PATSTAT_control_variable\Control_variable_Datastream.xlsx"
	OUT=Datastream_bdate DBMS=XLSX replace;
	Sheet="bdate";
	RANGE="A1:B12034";
	GETNAMES=YES;
RUN;

it is shown as 

#NAME?	BDATE
77605Q	40786
15455F	37655
286279	36593
31192X	38765
130165	33218
130298	34387
130412	34396
13052M	36907

However, I expect to import them as the date value or character value?

thanks in advance

[Tom · Accepted solution]

---

@Alexxxxxxx wrote:

Dear all,

I have the excel file looks like this

#NAME?	BDATE
77605Q	31/08/2011
15455F	03/02/2003
286279	08/03/2000
31192X	17/02/2006
130165	11/12/1990
130298	22/02/1994
130412	03/03/1994
13052M	16/01/2001

When I import this excel file into SAS by the following code,

proc import datafile = "G:\PATSTAT_control_variable\Control_variable_Datastream.xlsx"
	OUT=Datastream_bdate DBMS=XLSX replace;
	Sheet="bdate";
	RANGE="A1:B12034";
	GETNAMES=YES;
RUN;

it is shown as 

#NAME?	BDATE
77605Q	40786
15455F	37655
286279	36593
31192X	38765
130165	33218
130298	34387
130412	34396
13052M	36907

However, I expect to import them as the date value or character value?

thanks in advance

---

The only reason BDATE variable in SAS would have values like that would be if that column in Excel had a mix of date values and character strings. In that case SAS would create a character value (you can store numbers as string, but you cannot easily store text as numbers).  When SAS (excel?) does that with dates you get the actual number that Excel stores, but as a string of digits.

The right fix is to find the character values in the Excel file and convert them to actual date values.  Then the data will import as a numeric field with a date format attached and SAS will adjust the number to match the numbers SAS uses for those date.

If you have to deal with the text you have in BDATE then first convert it to a number and then adjust the number to reflect how SAS stores dates compared in how Excel does. Make sure to attach a date format so the numbers are displayed in human readable form.

data want;
  set have;
  date=input(bdate,32.)+'30DEC1899'd ;
  format date yymmdd10.;
run;