SAS EM :Merging 2 data sets with a common ID- one set has multiple records for a unique ID

[Piyu]

Im using SAS Enterprise miner and want to know if its possible to do the following- if yes, then how?

Would much appreciate any support provided.

I have two data sets that I'm trying to Merge using a common Student_ID and create a new dataset.

Dataset 1 : every entry has a unique studentID

Student_ID   Age        Course      startYear

119                   24            Bsc          2014

Dataset 2: has multiple records for a studentID as it has a new entry for every subject the student is taking

Student_ID     sub_name       marks      Sub_year_level

119                   Botany1          60                    2

119                   Anatomy          70                   2

119                   cell bio             75                   3

129                   Physics1          78                   2

129                   Math1              60                   1  

i want to merge the two sets so that I take all records & all columns from dataset1 and create new columns where I want to get from dataset 2 the average mark(has to be calculated)  for each subject_year level per student. So the final dataset will have unique Student_Id in all records 

What I want my new dataset to look like:

Student_ID   Age        Course      startYear  level1_avg_mark     level2_avg_mark     level3_avg_mark

119                   24            Bsc          2014                 60                           65                         70

If calculating avg marks from dataset 2vis not possible -still if I can get the student marks from dataset 2 merged to dataset 1 in some way that too would be great

[Astounding]

Although I'm not a user of EM, I expect the steps would be similar to a Base SAS approach.

(1) Working with the second data set only, get the average marks for each student/level.  Expect to see these fields in the results:  Student_ID, Level, Avg_Mark.  Each Student_ID could have multiple observations (one per level).

(2) Transpose that result, to get Student_ID, Avg_Mark_Level1, Avg_Mark_Level2, Avg_Mark_Level3.

(3) Merge by Student_ID with the first data set.

I know how to accomplish these steps in base SAS, but suspect that the tools within EM might be different.  Still, those steps would work (assuming they can be done in EM).

[Piyu]

Thank you.

Would be great if I can know how to do step 1 & 2 in SAS EM .

I can manage step 3

[LinusH]

I'm no EM user either, but I don't that data transformations belongs in EM. It's rather a feature of Data Integration Studio, Enterprise Guide or SAS Studio.