BookmarkSubscribeRSS Feed
sasphd
Lapis Lazuli | Level 10

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

sasphd_0-1642441944940.png

thanks a lot

4 REPLIES 4
Reeza
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
DYYYYMMDD

sasphd
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?

 

Reeza
Super User
You've provide data as an image, can't work with that.
You have not provide what you want as the output.
Kurt_Bremser
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;
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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 486 views
  • 0 likes
  • 3 in conversation