10-27-2015 09:21 AM
I am using SAS Enterprise guide and I want to import multiple sheets from excel to SAS. In excel first sheet has column headers but other sheets don’t have column headers.
Now I want to import all the sheets at the same time in SAS then append all in one append to the first dataset created form sheet1 as it has column headers.
I would appreciate your help.
I found something on the web so I tried using following code but library is not getting assigned.
LibName xlsLib ‘H:\Pneumonia.xls’;
I am getting error:
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
10-27-2015 09:28 AM
There is a simple way using macro to determine the Sheet you need.
Do the following example do this:
%macro pim(sheet); proc import out= payment datafile = 'E:\SAS DOC\project\Credit Banking.xls' dbms = Excel; sheet = "&sheet"; getnames = yes; run; %mend piim; %pim(Customer Acqusition); %pim(Spend); %pim(Repayment);
Hope this helps
10-27-2015 09:37 AM
Well, do you have the required SAS/ACCESS license? Run proc setinit. You can try the libname excel statement, described;
Its easy enough to work with. What I would say however, is all your data the same on all the sheets, Excel is not a structured format, hence if you have mix of chars/numbers, specials, dates are always tricky, you are going to have difficulties. Personally I would get the data in proper data transfer format and have an agreement with the vendor of what is supplied.
10-27-2015 09:48 AM
Please note only first sheet has variable names on the top and other sheets don’t. So when I import each sheet separate, 2/3/4 sheets will have variables as A, B, C after they get imported in SAS…if I do:
getnames = no;
And then append is a problem.
on the other hand if I have getnames = yes; first row will be displayed as varaible names..that's also a problem in appending all these sheets together in SAS.
10-27-2015 10:18 AM
Anna, So for the sheet that does not contain the column names try this:
%macro pim(sheet,start,namerow); proc import out= payment datafile = 'E:\SAS DOC\project\Credit Banking.xls' dbms = Excel; startrow=&start; namerow=&namerow.; sheet = "&sheet"; getnames = yes; run; %mend piim; %pim(Customer Acqusition,2,3); %pim(Spend,1,1); %pim(Repayment,1,1);
03-09-2017 02:38 PM
What is "necro'ing"? I'm sorry, I'm new here. I learned from this code what I needed to do and it worked. It was very helpful and to me, "Outstanding". What's the issue? I'm sorry, I do not understand.
03-09-2017 02:44 PM
To necro a post is to raise it from the dead. This thread is from 2015, it is very old. Posting again to an old thread raises it back up to the forefront, I for instance know this because I get automatic emails from posts I have contributed to, so as you can imagine, if people starting posting in all old posts, my email would collapse. You can show your apreciation to posts by clicking the Like button below the post.
Also note that old posts my not use current thinking/technology. For instance if you are using an XLSX file, and have SAS 9.4 then you can far simpler access data by using the libname excel engine: