BookmarkSubscribeRSS Feed
Piyu
Fluorite | Level 6

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

3 REPLIES 3
Astounding
PROC Star

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
Fluorite | Level 6

Thank you.

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

I can manage step 3

LinusH
Tourmaline | Level 20

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.

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 767 views
  • 2 likes
  • 3 in conversation