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