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.
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;
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.
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.