import data from excel with a correct date type

[sasphd]

Hello 

I want to import data from excel. the date type in the first row are date excel format, but SAS import it as text format. this is my program 

proc import datafile = 'C:\MaRecherche\Bank Fraud Announcement/MS_price_data_200firm.xlsx' DBMS = xlsx OUT = _6_Morningstar_Data;   

this is the output sas for the following date in the first row 

1 janvier 1999

2 janvier 1999

3 janvier 1999

4 janvier 1999

5 janvier 1999

6 janvier 1999

thanks a lot

[Reeza]

Well, SAS dates cannot start with a number so that's one reason why. What do you want the dates to be?

You can add the option:

option validvarname = ANY;

Before your PROC IMPORT and you'll get names that look like what you have and any time you want to refer to the date you'll need to type it out like
'1 janvier 1999'n.

I would suggest dates such as D19990101 D19990102
DYYYYMMDD

[sasphd]

Hi Reeza, 

Thanks for your help

it doses not work. is there any way to put date in excel in the good format. I put date in excel in date format so date is like 1 january 1999?

[Reeza]

You've provide data as an image, can't work with that.
You have not provide what you want as the output.

[Kurt_Bremser]

SAS uses the raw values as variable names in this case. Since you want to have such data in a long layout anyway (Maxim 19), transpose first, and convert to a SAS date in a final step:

options validvarname=any;
data have;
input secid :$10. "36161"n "36162"n;
datalines;
0P0000001C 36.61 36.81
;

proc transpose
  data=have
  out=long
;
by secid;
var "36"n:;
run;

data want;
set long;
date = input(_name_,5.) + '30dec1899'd;
format date yymmdd10.;
drop _name_;
run;

Use a RENAME statement or RENAME= dataset option to rename COL1 to a meaningful variable.