Input an excel file!

[vxhong17]

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,

[Reeza]

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]

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]

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)