03-15-2017 08:11 AM
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
03-15-2017 09:22 AM
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).
03-15-2017 10:22 AM
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.