BookmarkSubscribeRSS Feed
vxhong17
Calcite | Level 5

Dear SAS Experts,

 

Could you help me input an excel file with infile and outfile as in the attachment?

 

I need to input many excel files with different dates (over 10 years) and many variables. I only show a sample with three dates and one variable (Tot Return Ind).

 

Thank you so much for your help,

 

 

3 REPLIES 3
Reeza
Super User

Generally people don't download excel files here. 

 

How is a proc import working for your excel file? 

Or can you convert it to a CSV file?

 

If you've developed a sample, please post it in a message. 

vxhong17
Calcite | Level 5

Hi Reeza,

 

Thank you so much for your comments,

 

I employed "proc import" to input excel files. A sample is here.

#VALUE! 9/9/1987 9/10/1987 9/11/1987
SAMSUNG ELECTRONICS - TOT RETURN IND 341.28 343.76 338.04
KOREA ELECTRIC POWER - TOT RETURN IND NA NA NA
HYUNDAI MOTOR - TOT RETURN IND 428.57 430.72 418.21
HYUNDAI MOBIS - TOT RETURN IND NA NA NA

 

My code is:

 

PROC IMPORT datafile= "&path.\test1.xlsx" DBMS=xlsx OUT=Outfile REPLACE;
            sheet = "sheet1";
            GETNAMES=No;
            datarow=2;
RUN;

 

I need to get variables var1-var4 in outfile to do other steps. However, in outfile, variables are A, B, C, D.

 

I can not use "infile" because I do not know how many variables in excel files.

 

Thank you so much,

 

 

 

 

Kurt_Bremser
Super User

The first line in your Excel contains data that SAS cannot use for variable names. Make sure before you import that the first line contains valid SAS names (contains only characters a-z, 0-9 and underline, does not start with a digit, and has a maximum length of 32)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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