Help using Base SAS procedures

Transpose two variables at the same time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Transpose two variables at the same time

Hello,

The data I have is as follows:

IDScoreGrade
A9A
A5B
B8A
B3C

I want to transpose it as follows:

IDScore1Score2Grade1Grade2
A95AB
B83AC

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
Respected Advisor
Posts: 3,777

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

View solution in original post


All Replies
Super User
Posts: 10,530

Re: Transpose two variables at the same time

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

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
Super User
Posts: 7,413

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

  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;

Occasional Contributor
Posts: 13

Re: Transpose two variables at the same time

Thanks both, RW9 and data_null_ !

Respected Advisor
Posts: 3,777

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

3-11-2015 10-49-04 AM.png
Solution
‎03-11-2015 12:45 PM
Respected Advisor
Posts: 3,777

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 Smiley Surprisedbs from(select count(*) as obs from tests group by ID);
   quit;
proc summary data=tests nway;
  
class ID;
   output out=wide(drop=_Smiley Happy idgroup(out[&obs](score grade)=);
   run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 292 views
  • 9 likes
  • 5 in conversation