topic Re: To import an Excel file into SAS with column headers on more than one line. in SAS Programming

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

Santt0sh — Tue, 29 Mar 2022 01:51:16 GMT

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.

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

Santt0sh — Tue, 29 Mar 2022 02:01:49 GMT

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

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

Kurt_Bremser — Tue, 29 Mar 2022 06:09:46 GMT

Your attached Excel file is missing..

To import an Excel file into SAS with column headers on more than one line.

Santt0sh — Tue, 29 Mar 2022 12:23:44 GMT

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-two-rows/m-p/804672/emcs_t/S2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEwxQlFPV0dXOEkyQURCfDgwNDY3MnxTVUJTQ1JJUFRJT05TfGhL#M316904).

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 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.

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

Santt0sh — Tue, 29 Mar 2022 12:26:00 GMT

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-on-more/m-p/804730#U804730

Thank you for your time and guidance!!

Re: To import an Excel file into SAS with column headers on more than one line.

Tom — Tue, 29 Mar 2022 15:48:12 GMT

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?

Re: To import an Excel file into SAS with column headers on more than one line.

Tom — Tue, 29 Mar 2022 16:05:43 GMT

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;

Re: To import an Excel file into SAS with column headers on more than one line.

Santt0sh — Thu, 31 Mar 2022 12:00:06 GMT

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,