data test; input pk$ name$ no; cards; 1 a 10 2 a 12 3 a 13 4 b 15 5 b 16 6 c 17 I want to create 9 variables and move the obs like this if the name repeated 2 or 3 times this it should go to new variable like this In this bewlo example the name having a is repating 3 times and b is repating 2 times and name having c is only once like this i wnat the output pk name no pk2 name2 no2 pk3 name3 no3 1 a 10 2 a 12 3 a 13 4 b 15 5 b 16 6 c 17 If data is messy you can refer the text
data test;
input pk$ name$ no;
cards;
1 a 10
2 a 12
3 a 13
4 b 15
5 b 16
6 c 17
;
data want;
set test;
by name;
count+first.name;
run;
proc transpose data=want out=temp1(drop=_name_) prefix=pk;
var pk ;
by count;
run;
proc transpose data=want out=temp2(drop=_name_) prefix=name;
var name ;
by count;
run;
proc transpose data=want out=temp3(drop=_name_) prefix=no;
var no;
by count;
run;
data final(drop=count);
retain pk1 name1 no1 pk2 name2 no2 pk3 name3 no3;
merge temp1-temp3;
by count;
proc print;run;
another way:
data test;
input pk$ name$ no;
cards;
1 a 10
2 a 12
3 a 13
4 b 15
5 b 16
6 c 17
;
data temp;
set test;
by name;
count+first.name;
run;
proc sort;
by count pk;
proc sql noprint;
select max(cct) into : max
from (select count(*) as cct from temp group by name);
quit;
%put &max;
%let max=%trim(%left(&max));
data want;
set temp;
by count;
array p{*}$ pk1-pk&max;
array n{*}$ name1-name&max;
array o{*} no1-no&max;
retain pk: name: no:;
if first.count then ct=0;
ct+1;
p{ct}=pk;
n{ct}=name;
o{ct}=no;
if last.count then do;output; call missing(of p{*} n{*} o{*});end;
drop pk name no ct count;
run;
proc print;run;
Obs pk1 pk2 pk3 name1 name2 name3 no1 no2 no3
1 1 2 3 a a a 10 12 13
2 4 5 b b 15 16 .
3 6 c 17 . .
Linlin
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.