## Transpose two variables at the same time

Solved
Occasional Contributor
Posts: 13

# Transpose two variables at the same time

Hello,

The data I have is as follows:

 ID Score Grade A 9 A A 5 B B 8 A B 3 C

I want to transpose it as follows:

 ID Score1 Score2 Grade1 Grade2 A 9 5 A B B 8 3 A C

However, I'm not able to transpose two variables at the same time -- prefix doesn't take two variables. I'm transposing one variable at a time and then merging the two datasets. Could this be done in one step?

Thanks,

P

Accepted Solutions
Solution
‎03-11-2015 12:45 PM
Posts: 3,852

## Re: Transpose two variables at the same time

To calculate max obs per ID you can do this.  This works with max obs per ID of 100 or less.  One nice feature of this "transpose" in addition of multiple variables is the data types are preserved.

proc sql noprint;

select max(obs) into bs from(select count(*) as obs from tests group by ID);
quit;
proc summary data=tests nway;

class ID;
run;

All Replies
Super User
Posts: 13,498

## Re: Transpose two variables at the same time

Are there always exactly 2 values for ID? If so:

set have;

by id notsorted;

if first.id then do;

score1=score;

end;

if last.id then do;

score2=score;

output;

end;

run;

might work

SAS Employee
Posts: 232

## Re: Transpose two variables at the same time

Hi - just wanted you to know that I moved this message from the 'about communities" community into the SAS Procedures community. Thanks!

Occasional Contributor
Posts: 13

## Re: Transpose two variables at the same time

Thanks, but there are varying number of records for each ID.

Super User
Posts: 9,599

## Re: Transpose two variables at the same time

You would need to decide what the driving maximum is then.  In the code below I assume max count of grades with be the maximum number of columns.  Also note, that I put extra code in the final step, whilst in your example it works fine I add this code to cover times where one or the other is missing (as it would retain previous in that instance:

data have;

infile datalines;

input ID \$ Score Grade \$;

datalines;

A 9 A

A 5 B

B 8 A

B 3 C

;

run;

proc sql noprint;

select  max(CNT)

into    :TOT_ARRAY

from    (select ID,COUNT(GRADE) as CNT from WORK.HAVE group by ID)

group by ID;

quit;

data want (drop=array_cnt i score grade);

set have;

array score_{&TOT_ARRAY.} 8.;

by id;

if first.id then array_cnt=1;

score_{array_cnt}=score;

array_cnt=array_cnt+1;

if last.id then do;

output;

do i=1 to &TOT_ARRAY.;

score_{i}=.;

end;

end;

run;

Occasional Contributor
Posts: 13

## Re: Transpose two variables at the same time

Thanks both, RW9 and data_null_ !

Posts: 3,852

## Re: Transpose two variables at the same time

You can use PROC SUMMARY.  You do have to know or calculate the max obs by ID 2 in your.

data tests;
infile cards expandtabs;

input ID :\$1. score grade :\$1.;

cards;
A  9  A
A  5  B
B  8  A
B  3  C
;;;;
run;
proc print;

run;
proc summary data=tests nway;

class ID;
run;
proc print;

run;

Solution
‎03-11-2015 12:45 PM
Posts: 3,852

## Re: Transpose two variables at the same time

To calculate max obs per ID you can do this.  This works with max obs per ID of 100 or less.  One nice feature of this "transpose" in addition of multiple variables is the data types are preserved.

proc sql noprint;

select max(obs) into bs from(select count(*) as obs from tests group by ID);
quit;
proc summary data=tests nway;

class ID;