topic Re: I Need help on transformation to read in column names in SAS Programming

I Need help on transformation to read in column names

LightJade — Thu, 17 Sep 2020 21:20:08 GMT

Hi!

I need some help on how to do below transformation (from table 1 to table 2),

In my example in table 1, student 1 has been in City02, City03 and City250 of the 250 cities, so I put '1' for each of the 3 cities he went to.

Student 2 has been in City01, City03 of the 250 cities, so I put '1' for each of the 2 cities he went to.

In my output table 2, I want to list all the cities the students went to in one column and omit the cities that they never visited.

Can anyone give me help how to get table 2 as the output dataset from table 1 in sas?

Greatly appreciate!

Table 1
	City01	City02	City03	…	City250
Student 1	0	1	1	0	1
Student 2	1	0	1	0	0

Table 2
	City
Student 1	City02
Student 1	City03
Student 1	City250
Student 2	City01
Student 2	City03

Re: I Need help on transformation to read in column names

Kurt_Bremser — Thu, 17 Sep 2020 21:40:57 GMT

proc transpose data=have out=want (rename=(_name_=city) where=(col1=1));
by student;
var city:;
run;

Re: I Need help on transformation to read in column names

LightJade — Fri, 18 Sep 2020 15:02:50 GMT

Hi KurtBremser, thank you so much for above reply. Sorry I was tweaking my original data tables a bit before the posting, so when I applied your code to my true data, the output gives 0 obs.

So I reorganized my data tables in below way to be exactly the same as my true data

Table 1
Student_ID	City001	City002	City003	…	City250
A1F23	0	1	1	0	1
MN19B	1	0	1	0	0

Table 2
Student_ID	City
A1F23	City002
A1F23	City003
A1F23	City250
MN19B	City01
MN19B	City03

May I have your insight how to tweak your above code to make it work?

Thank you very much!!

Re: I Need help on transformation to read in column names

Kurt_Bremser — Fri, 18 Sep 2020 16:56:06 GMT

Just use the correct variable name (student_id) in the BY statement.

You can avoid such misunderstandings (because I had to make assumptions about your data) by posting data in the proper manner (as SAS code, data steps with datalines).