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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.