BookmarkSubscribeRSS Feed
Lapis Lazuli | Level 10


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

Super User
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

Lapis Lazuli | Level 10

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?


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

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;
0P0000001C 36.61 36.81

proc transpose
by secid;
var "36"n:;

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

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



Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.


Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3 in conversation