Import and merge multiple sheets from excel

[mayasak]

Hi,

I want to import multiple Excel sheets into SAS and merge them into one file using macro. I wrote a macro but each call of the macro creates/overwrites the same dataset. Is there a way to keep the worksheets and merge them into one file?

Here's the macro:

proc import out= PQS replace
datafile = "C:\Users\c227466\Desktop\Testing Testing/PQImport.xlsx"
dbms = Excel;
sheet = "&sheet";
getnames = yes;
run;
%mend data;

%data(disease incidence);
%data(efc);
%data(lab info);

Thank you

[maguiremq]

proc import out= PQS replace
datafile = "C:\Users\c227466\Desktop\Testing Testing/PQImport.xlsx"
dbms = Excel;
sheet = "&sheet";
getnames = yes;
run;

You're overwriting your SAS data set, PQS, each time you run the macro.

You can add another macro parameter to specify a valid SAS data set name, like this:

%macro(sheet, name)
proc import out= &name replace
datafile = "C:\Users\c227466\Desktop\Testing Testing/PQImport.xlsx"
dbms = Excel;
sheet = "&sheet";
getnames = yes;
run;
%mend data;

%data(disease incidence, disease_incidence);
%data(efc, efc);
%data(lab info, lab_info);

You would then need to decide whether you want to `MERGE` or concatenate the data sets. I don't know if you have any keys/identifiers in your data set(s) that allow you to `MERGE`.

I'm going to assume that you want to concatenate. If you need to concatenate, you could use this method, though there are many more ways of doing it:

data all_files;
set disease_incidence efc lab_info;
run;

 Code is untested.

[mayasak]

Thank you, Maguiremq. This worked perfectly fine. 

I'm trying to merge those 3 tables into one table (TB1) and then merge TB1 with another table (NHS) that I import either as a SAS file or an excel file.

The issue is that I don't have the same key/identifier in all tables. The main table is the disease_incidence which can be joined to other tables with different key identifiers.  So I'm wondering how I can streamline this merge.

Thank you

[maguiremq · Accepted solution]

Can you provide example data? This macro takes the data set and transforms it into a reproducible data set so that we can see how your data works. You would need to do it for all three data sets.

If your data contains PHI or otherwise confidential information, attempt to mimic it with a DATA step with a DATALINES statement This is an example of how to do it. 

When you say that you don't have the same key/identifier in all tables, do you mean that the name for the common key is not the same across all tables?

If so, you could use PROC SQL and the ON clause to say:

inner join on a.key = b.other_key

Otherwise, you can use the MERGE statement and rename your keys accordingly.

Without seeing the data, though, we will have a difficult time answering the question.

[mayasak]

Here are some data examples, hope this helps.

data Disease_Inc;
input Person_Id Incident_ID MRN $ Last_Name $ First_Name $ Gender $;
datalines;
122345 56788 M776 Doe John M
34567 12344 L87765 Mouse Mickey M
90876 36476 009875 Mouse Minnie F
777654 227364 26376 Duck Donald M
56761 23676 M765 Pippin Mary F
667785 3624554 L97960 Smith Oliver M
345564 34567 9432 Johns Karen F
79908 123540 M78765 Doe Kevin M
677654 378754 L8544 Wayne Bruce M
run;

data Disease_Lab;
input Incident_ID Last_Name $ First_Name $ Gender $;
datalines;
56788 Doe John M
127655 Johns Trevor M
36476 Mouse Minnie F
297654 Gatzby Jay M
23676 Pippin Mary F
3624554 Smith Oliver M
34857 Miller Edward M
176255 Roberts Linda F
378754 Wayne Bruce M
854324 Hill Jane F
run;

data Disease_EFC;
input Person_Id Incident_ID Last_Name $ First_Name $ Gender $;
datalines;
34567 12344 Mouse Mickey M
90876 36476 Mouse Minnie F
777654 227364 Duck Donald M
56761 23676 Pippin Mary F
661234 364004 Rosa Rhona M
776567 755320 Luis Robert M
run;

data Disease_NHS;
input MRN $ Last_Name $ First_Name $ Gender $;
datalines;
M776 Doe John M
L87765 Mouse Mickey M
26376 Duck Donald M
98332 Roe Simon M
9432 Johns Karen F
M000911 Mill Lora F
L8544 Wayne Bruce M
S78524 Hill Jane F
run;

Thank you

[Sajid01]

Hello @mayasak 
your possible solutions are here

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/040-30.pdf 

https://www.pharmasug.org/proceedings/2011/CC/PharmaSUG-2011-CC10.pdf 

[mayasak]

Hi Sajid,

Thank you for the documents. I tried the first one using the path and file as follows:

%macro xl2sas(Path=C:\Users\c227466\Desktop\Testing Testing\,
File=Power Query Import.xlsx,
StartRow=1,
StartCol=1,
GetVarName=1);

Then added the rest of the codes as mentioned in the document but I got the following message when I double-clicked on the Xl2sas folder

I'm not sure if I have to manipulate anything in the code except for the "path" and "filename"!

Thank you

[Sajid01]

Experts in this forum have time and again advised that before a macro like this is created, test the code itself.
wondering if that was done. If not do.
The second observation I would like to make is about the statement

File = Power Query Import.xlsx ;

If there are spaces in the filename enclose the filename in quotes OR better rename the file replacing spaces with underscore. Thus it should be either of the following

File = "Power Query Import.xlsx" ;
OR 
File =Power_Query_Import.xlsx

Please test the code first and then create the macro.