Transpose two variables at the same time

[pmesh]

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

[ballardw]

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

[Community_Help]

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

[pmesh]

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

[RW9]

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;

  end;

run;

[pmesh]

Thanks both, RW9 and data_null_ !

[data_null__]

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;

[data_null__ · Accepted solution]

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;