Merging tables from multiple excel files

[Mil00]

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.

[PaigeMiller]

Show us what you have tried so far.

[Mil00]

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

[Mil00]

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

[PaigeMiller]

It's not spam to ask SAS questions here.

I do not provide technical support via e-mail.

[ballardw]

@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.

[Patrick]

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;