CODEWITHSUNDEEP
sas
kl78
kl78

Reputation: 1666

How to transpose Table in a specific way

This is some example data, real data is more complex, other fields and about 40000 observations and up to 180 values per id (i know that i will get 360 rows in transposed table, but thats ok):

Data have;
input lastname firstname $ value;
datalines;
miller george 47
miller george 45
miller henry 44
miller peter 45
smith peter 42
smith frank 46
;

run;

And i want it to transpose in this way, so I have lastname, and then alternating firstname and value for ervery line matching the lastname. data want:

Lastname   Firstname1 Value1 Firstname2 value2 Firstname3 Value3 firstname4 value4
miller     george     47     george     45     henry      44     peter      45
smith      peter      42     frank      46

I tried a bit with proc transpose, but i was not able to build a table exactly the way i want it, described above. I need the want table exactly that way (real data is more complex and with other fields), so please no answers which propose to create a want table with other layout.

Upvotes: 0

Views: 95

Answers (1)

Longfish
Longfish

Reputation: 7602

proc summary has a very useful function to do this, idgroup. You need to specify how many values you have per lastname, so I've included a step to calculate the maximum number.

Data have;
input lastname $ firstname $ value;
datalines;
miller george 47
miller george 45
miller henry 44
miller peter 45
smith peter 42
smith frank 46
;
run;

/* get frequency count of lastnames */
proc freq data=have noprint order=freq;
table lastname / out=name_freq;
run;

/* store maximum into a macro variable (first record will be the highest) */
data _null_;
set name_freq (obs=1);
call symput('max_num',count);
run;

%put &max_num.;

/* transpose data using proc summary */
proc summary data=have nway;
class lastname;
output out=want (drop=_:)
            idgroup(out[&max_num.] (firstname value)=) / autoname;
run;

Upvotes: 4

Related Questions