topic Re: Merging tables from multiple excel files in New SAS User

Merging tables from multiple excel files

Mil00 — Thu, 12 Mar 2020 20:06:31 GMT

I have 4 excel files to merge into one output (proc print will be enough). Each file has a table with two variables: 'Student_no' and 'Mark'.

Student_no always stays the same, but marks for each file is different, e.g.

File 1:

Student_no Mark1

d5476 87

d7389 46

d1276 58

File2:

Student_no Mark2

d5476 67

d7389 52

d1276 96

etc.

I need to create one table as an output which should look like this:

Student_no Mark1 Mark2 Mark3 Mark4

d5476 67 67 74 45

d7389 52 56 89 57

d1276 96 67 57 93

Thank you in advance.

Re: Merging tables from multiple excel files

PaigeMiller — Thu, 12 Mar 2020 20:08:22 GMT

Show us what you have tried so far.

Re: Merging tables from multiple excel files

Mil00 — Fri, 13 Mar 2020 00:01:44 GMT

Is there any way I can contact you through email? Don't want to spam here too much

Re: Merging tables from multiple excel files

Mil00 — Fri, 13 Mar 2020 00:19:55 GMT

I don't have access to the PC right now. I have tried building a few codes, but it didn't work for me. I think that I am making it way too complicated than it should be

Re: Merging tables from multiple excel files

PaigeMiller — Fri, 13 Mar 2020 11:15:19 GMT

It's not spam to ask SAS questions here.

I do not provide technical support via e-mail.

Re: Merging tables from multiple excel files

ballardw — Fri, 13 Mar 2020 19:34:40 GMT

@PaigeMiller asks what you have attempted so far because it shows you have tried and sometimes we only need to add a detail or two as your solution may be very close.

Or if you have run code and are having issues related to warnings or errors then you should copy the log entry and paste the log text into a code box opened on the forum using the {I} or "running man" icon.

Hint:

Stare with the SAS data sets you have.

Sort by the common key variable.

Data step merge by that variable.

Warning: Because your data starts in Excel there may be added steps to RENAME variables needed.

Re: Merging tables from multiple excel files

Patrick — Mon, 16 Mar 2020 02:58:22 GMT

Assuming you're able to read the Excel data into SAS tables here how to combine the data (the bit for the 4th table for you to add).

data tbl1;
  input Student_no $ Mark;
  datalines;
d5476 87
d7389 46
d1276 58
;

data tbl2;
  input Student_no $ Mark;
  datalines;
d5476 67
d7389 52
d1276 96
;

data tbl3;
  input Student_no $ Mark;
  datalines;
d5476 67
d9999 99
d1276 96
;

proc sort data=tbl1;
  by student_no;
run;
proc sort data=tbl2;
  by student_no;
run;
proc sort data=tbl3;
  by student_no;
run;

data want;
  merge 
    tbl1(rename=(mark=mark1))
    tbl2(rename=(mark=mark2))
    tbl3(rename=(mark=mark3))
    ;
  by student_no;
run;
proc print data=want;
run;