BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pmesh
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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;

View solution in original post

7 REPLIES 7
ballardw
Super User

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
SAS Employee

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

pmesh
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Thanks both, RW9 and data_null_ !

data_null__
Jade | Level 19

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;
 
3-11-2015 10-49-04 AM.png
data_null__
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5326 views
  • 13 likes
  • 5 in conversation