Data transpose question

[deleted_user]

Hello,
I am new to SAS, so I apologize if the question is too simple, I have created an Information and I know how to open this in EG. The Information map contains info about the courses a student has taken, so if the student has taken 4 courses, I have 4 rows of data for that student.
Now I am trying to change this so that a student only has 1 row of data and the infromation about the 4 courses(or any number of courses) show up as additional columns for this students, so I am thinking transpose but I could be totally wrong. Is there a way to transform the data from the information map to result in the data I am looking for.
Here is a sample

Input data
Student1 name dob, teachernm school
course1
course2
course3

Now I want to the output data to be
student1 name dob teachernm_course1 school_course1 teacher_nm_course2 school_course2

I would really appreciate if any one can point me in the direction on how to do this.
If I posted this in the wrong forum I apologize

Thanks

[RichardH_sas]

Welcome to using SAS!

There's a task in SAS Enterprise Guide (EG) called Transpose Data, which could be used. However, there's another task which might be even better for what you're trying to do called split columns. That's essentially what you want to do here: split the teachernm column and split the school column. The only tricky part is that the Split Columns task limits you to splitting one column at a time. So, we can use two Split Columns tasks and then join the results.

Click the input data source then select Tasks \ Data \ Split Columns in EG 4.2 (if you have EG 4.1, select Data \ Split Columns). For the task roles, use Teachernm as the Column to split; Course as the Value identifier column; and name, dob as the Group analysis by columns. In the Results pane, specify an output location and do use a prefix, something like Teacher_nm_.

Now, use a second split columns task on the data source. Same idea, but use School as the Column to split, school_ as the prefix, and output the data to a different table. You should now have two new tables you've created.

Use a query builder (right-click on either table, and select Query Builder or Filter and Query) to join the tables. Join based on name and dob. Include all the columns in the new table and you should be good to go!

[deleted_user]

Richard,
Thanks for your response, I think the split columns will work but I am running into a problem, I do not have a field which is unique for the student in terms of the courses taked, the courseID is unique to the entire data set, so if I split by putting course as the value identifier, and if the data set has 50 courses I'll 50 columns for teachers and 50 columns for schools.
So I am thinking is there any way I can add a field which will have a unique value for each a particular student has taken, the maximum no. of courses a student can take is 8 so I have a column like this(I don't know yet how to create this column) then if I use this as a value identifier then I'll have 8 columns of teachers and 8 columns of school do you think I can do something like this

[RichardH_sas]

Ah, I see what you're saying. I'm playing around right now and am blanking on a simple way to do this. So, here's my best attempt -- I'll post again if I figure out something simpler!

1. New plan: let's use two Transpose Tasks instead of Split Columns. For the first Transpose task, Transpose variable is: Teachernm. Group analysis by variables are: name, dob. In the Options pane, set a prefix of Teachnm_course. The transpose task will automatically handle the numbering (1,2,3,...).

2. Second transpose task, Transpose variable is: school. Group analysis by variables are: name, dob. In the Options pane, set a prefix of school_course.

3. Use a Query Builder to join these two tables. Join based on name, dob. Drag all the columns into the select data area except the columns named SOURCE or LABEL.

That should work. Again, if I figure out a better way to do it, I'll let you know. The other solution I came up with involved Transpose, then Stack Columns, then a Query Builder, then Split Columns. Not pretty! 🙂

[deleted_user]

Thanks Richard, the transpose option worked better than the split columns option. I had around 5 columns that needed to be transposed, so I had to transpose 5 times and then use the query builder to join all of them.

Thanks Again
Shri