DATA Step, Macro, Functions and more

Input an excel file!

Reply
Contributor
Posts: 51

Input an excel file!

[ Edited ]

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,

 

 

Super User
Posts: 17,849

Re: Input an excel file!

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. 

Contributor
Posts: 51

Re: Input an excel file!

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,

 

 

 

 

Super User
Posts: 6,946

Re: Input an excel file!

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 349 views
  • 0 likes
  • 3 in conversation