topic Re: SAS EM :Merging 2 data sets with a common ID- one set has multiple records for a unique ID in SAS Data Management

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

Piyu — Wed, 15 Mar 2017 12:11:30 GMT

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

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

Astounding — Wed, 15 Mar 2017 13:22:19 GMT

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

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

Piyu — Wed, 15 Mar 2017 13:41:54 GMT

Thank you.

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

I can manage step 3

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

LinusH — Wed, 15 Mar 2017 14:22:53 GMT

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.