topic Re: Transpose two variables at the same time in SAS Procedures

Transpose two variables at the same time

pmesh — Wed, 11 Mar 2015 15:21:45 GMT

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,

Re: Transpose two variables at the same time

ballardw — Wed, 11 Mar 2015 15:28:56 GMT

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

Data want (drop= score grade);

set have;

length grade1 grade2 $ 1;

retain score1 grade1;

by id notsorted;

if first.id then do;

score1=score;

grade1=grade;

end;

if last.id then do;

score2=score;

grade2=grade;

output;

end;

run;

might work

Re: Transpose two variables at the same time

Community_Help — Wed, 11 Mar 2015 15:38:37 GMT

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

Re: Transpose two variables at the same time

pmesh — Wed, 11 Mar 2015 15:38:54 GMT

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

Re: Transpose two variables at the same time

data_null__ — Mon, 26 Aug 2019 18:26:58 GMT

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;
   output out=wide(drop=_:) idgroup(out[2](score grade)=);
   run; 
proc print; 
   run;

Re: Transpose two variables at the same time

RW9 — Wed, 11 Mar 2015 15:59:02 GMT

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.;

array grade_{&TOT_ARRAY.} $1.;

by id;

retain array_cnt score_1-score_%trim(&TOT_ARRAY.) grade_1-grade_%trim(&TOT_ARRAY.);

if first.id then array_cnt=1;

score_{array_cnt}=score;

grade_{array_cnt}=grade;

array_cnt=array_cnt+1;

if last.id then do;

output;

do i=1 to &TOT_ARRAY.;

score_{i}=.;

grade_{i}="";

end;

run;

Re: Transpose two variables at the same time

data_null__ — Wed, 11 Mar 2015 16:45:19 GMT

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 :obs from(select count(*) as obs from tests group by ID);
   quit; 
proc summary data=tests nway; 
   class ID;
   output out=wide(drop=_:) idgroup(out[&obs](score grade)=);
   run;

Re: Transpose two variables at the same time

pmesh — Wed, 11 Mar 2015 16:46:36 GMT

Thanks both, RW9 and data_null_ !