Import an excel sheet which has the column header on two rows

[Santt0sh]

Hi All,

I'm trying to read an excel file into SAS which has headers on two rows, I tried using the solutions provided here but I was not able to get the desired output.

I thought of reading the .xlsx file twice and assigning the variable names to the merged columns in excel but later realized that the excel sheet/dataset keeps growing for the upcoming months or years.  

I also tried to transpose after importing the .xlsx file with GETNAME = NO OPTION.

Please find the excel attached, Kindly Suggest.

[Santt0sh]

The transpose results.
Variable Name Variable Type
C 2
E 2
F 2
G 2
GRAND TOTAL 1
H 2
I 2
J 2
K 2
L 2
M 2
N 2
O 2
PROGRAM_TYPE 2
PRODUCT_TYPE 2
R 2
S 2
T 2
U 2
V 2
W 2
X 2
Y 2
_2020 2
_2021 2

[Kurt_Bremser]

Your attached Excel file is missing..

[Santt0sh]

Hi Kurt,

Thank you for your quick response!

I was not able to upload the file to the existing post.
Kindly find the attachment in the below post.
https://communities.sas.com/t5/SAS-Programming/To-import-an-Excel-file-into-SAS-with-column-headers-...


Thank you for your time and guidance!!

[Santt0sh]

Hi All,

Please accept my Sincere Apologies for posting the same question again.

I missed the attachment in my previous post and I'm not sure how to add an attachment to the existing post (https://communities.sas.com/t5/SAS-Programming/Import-an-excel-sheet-which-has-the-column-header-on-....

I'm trying to read an excel file into SAS which has headers on two rows, I tried using the solutions provided here but I was not able to get the desired output.

I thought of reading the .xlsx file twice and assigning the variable names to the merged columns in excel but later realized that the excel sheet/dataset keeps growing for the upcoming months or years.

I also tried to transpose after importing the .xlsx file with GETNAME = NO OPTION.

The transpose results.
Variable Name Variable Type
C 2
E 2
F 2
G 2
GRAND TOTAL 1
H 2
I 2
J 2
K 2
L 2
M 2
N 2
O 2
PROGRAM_TYPE 2
PRODUCT_TYPE 2
R 2
S 2
T 2
U 2
V 2
W 2
X 2
Y 2
_2020 2
_2021 2

Please find the excel attached, Kindly Suggest.

[Tom]

Here is how this Forum displays that XLS file.

So how many variables do you see there?

What are the names you would want for them?

[Tom · Accepted solution]

Just tell it to skip the first row.

proc import dbms=xls datafile="&path/&fname"
  out=example replace
;
 range='$A2:';
run;

Results:

You could clean up the first 3 columns easily using RETAIN.

data step1;
  length program_type product_type $20;
  retain program_type product_type;
  set example;
  if A ne ' ' then do;
     program_type=A;
     product_type=' ';
  end;
  if B ne ' ' then product_type=B;
  rename c=Line_item q=TOTAL;
  drop A B;
run;

[Santt0sh]

Hi Kurt,

Thank you for your Post!!
Using the below mentioned code I was able to read the Excel files with headers on Multiple rows, and I was working on renaming the columns as the excel sheet keeps growing and also calculating the sales percentages for all the years.

Regards,