import excel file into SAS

Alexxxxxxx — Mon, 24 Feb 2020 16:28:25 GMT

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

Re: import excel file into SAS

Tom — Mon, 24 Feb 2020 16:47:09 GMT

@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;

topic Re: import excel file into SAS in SAS Programming

import excel file into SAS

Re: import excel file into SAS