topic Re: Proc Transpose - Repeated Values in SAS Procedures

Proc Transpose - Repeated Values

JAR — Mon, 13 Feb 2012 10:21:19 GMT

Dear All,

I have the following table

Teacher	Score	Student
Teacher1	A	Tom
Teacher1	A	Harry
Teacher1	B	Ron
Teacher2	A	Neval

I need to transpose it in this manner:

Teacher	A's	B's
Teacher1	Tom	Ron
Teacher1	Harry
Teacher2	Neval

Howerever, Proc Transose sends and error as ID (score) has duplicates.

Could someone help me to write a proc code so that when the repeatation happends, the entry must be moved to the next row (as in the second one)?

Thanking you in advance,

Sincerely,

Jijil Ramakrishnan

Proc Transpose - Repeated Values

art297 — Mon, 13 Feb 2012 13:38:24 GMT

If it doesn't matter which people get paired, as long as they are on the currect teacher/grade columns and variables, then you could use something like:

data have;

input (Teacher Score Student) ($);

cards;

Teacher1 A Tom

Teacher1 A Harry

Teacher1 B Ron

Teacher2 A Neval

;

data a (keep=Teacher A) b (keep=Teacher B);

set have;

if Score eq "A" then do;

a=Student;

b='ZZZZZZZZ';

output a;

output b;

end;

else if Score eq "B" then do;

b=Student;

a='ZZZZZZZZ';

output a;

output b;

end;

run;

proc sort data=a;

by teacher a;

run;

proc sort data=b;

by teacher b;

run;

data want;

set a;

set b;

if catt(a,b) ne 'ZZZZZZZZZZZZZZZZ';

if a eq 'ZZZZZZZZ' then call missing(a);

if b eq 'ZZZZZZZZ' then call missing(b);

run;

Re: Proc Transpose - Repeated Values

data_null__ — Mon, 13 Feb 2012 14:19:20 GMT

Are you willing to create one more variable?

dm 'clear log; clear output;';
data test;
   input teacher:$10. grade :$1. name :$8.;
   cards;
Teacher1 A Tom 
Teacher1 A Harry 
Teacher1 B Ron 
Teacher2 A Neval 
;;;;
   run;
proc sort data=test;
   by teacher grade name;
   run;
data test;
   set test;
   by teacher grade;
   if first.grade then index=0;
   index +1;
   run;
proc sort data=test;
   by teacher index grade;
   run;
proc transpose data=test out=want(drop=_name_);;
   by teacher index;
   var name;
   id grade;
   run;
proc print;
   run;

Re: Proc Transpose - Repeated Values

Astounding — Mon, 13 Feb 2012 14:28:11 GMT

JAR,

Here's an approach that is deceptively short. You have to understand some of the inner workings of the DATA step. In this case, you'll get a note about a many-to-many merge. It's harmless this time, but may not be in other DATA steps:

data final;

merge original (rename=(student=A) where=(score='A'))

original (rename=(student=B) where=(score='B'));

by teacher;

output;

A = ' ';

B = ' ';

run;

All of the replies so far are relying on your data containing just A and B. All could be extended to handle other values as well. But it's a harder problem to tackle if you don't know the values of SCORE ahead of time.

Good luck.

Re: Proc Transpose - Repeated Values

Linlin — Mon, 13 Feb 2012 14:57:48 GMT

Hi Data _null_,

Why did you use ';;;;' instead of ';'?

Thank you!

Re: Proc Transpose - Repeated Values

art297 — Mon, 13 Feb 2012 15:02:54 GMT

Linlin: a lot of people use ;;;; instead of ; to end a cards, datalines, cards4 or datalines4 section as they are needed for cards4 and datalines4 and don't pose a problem for the other two.

Cards4 and Datalines4 are necessary when the input contains a semi-colon

Re: Proc Transpose - Repeated Values

data_null__ — Mon, 13 Feb 2012 15:08:04 GMT

I like to be VERY careful. :smileygrin:

Re: Proc Transpose - Repeated Values

Linlin — Mon, 13 Feb 2012 15:19:20 GMT

Thank you Art and Data _null_!

Re: Proc Transpose - Repeated Values

Ksharp — Tue, 14 Feb 2012 04:13:44 GMT

data test;
   input teacher:$10. grade :$1. name :$8.;
   cards;
Teacher1 A Tom 
Teacher1 A Harry 
Teacher1 B Ron 
Teacher2 A Neval 
;;;;
   run;
proc sql noprint;
 select distinct cats('test(where=(teacher="',teacher,'" and grade="',grade,'") rename=(name=',grade,'))') into : list separated by ' '
  from test;
quit;
%put &list;
data want(drop=grade);
 merge &list ;
 by teacher;
 output;
 call missing(of _all_);
run;

Ksharp

Proc Transpose - Repeated Values

JAR — Tue, 14 Feb 2012 13:44:44 GMT

Your code is astounding! It worked perfectly.

There is some sort of elegance in this.

Thanks Mr. Atounding!

I wonder how would you have dealt with it, if the scores are not predetermined.

Jijil

Re: Proc Transpose - Repeated Values

Astounding — Tue, 14 Feb 2012 14:24:45 GMT

Jijil,

Thank you. If the scores are not predetermined, macro language gets involved. (I didn't check data_null_'s approach, but it might work for "normal" scores. It would run into trouble if the same data set had scores of both $ and # because the ID statement in PROC TRANSPOSE would try to create a variable named _ for both of those. A similar issue would arise if the data contained both A+ and A-.)

The details might be burdensome, but this is the approach I would take. Capture a list of distinct values for SCORE. (Be careful not to overcount if any of the SCORE values actually contains an embedded blank, such as A +.) Use two sets of loops in macro language to generate SCORE_01 - SCORE_nn as needed and to label them appropriately. For example, in the MERGE statement, one iteration through the loop would generate:

original (rename=(student=score_01) where=(score="&first_value_found"))

Use a second loop through the same values later, to generate labels, for example:

label score_01 = "Score was &first_value_found");

Even that might be a hair too simple if any of the original SCORE values contain double quotes, but that's the idea.

Good luck.