topic Reg:moving Obs in SAS Programming

Reg:moving Obs

sas_ — Sun, 29 Jan 2012 09:35:31 GMT

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

Reg:moving Obs

Linlin — Sun, 29 Jan 2012 14:51:03 GMT

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;

Re: Reg:moving Obs

Linlin — Mon, 30 Jan 2012 04:03:34 GMT

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