DATA Step, Macro, Functions and more

Reg:moving Obs

Reply
Contributor
Posts: 66

Reg:moving Obs

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

Attachment
Super Contributor
Posts: 1,636

Reg:moving Obs

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;

Super Contributor
Posts: 1,636

Re: Reg:moving Obs

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

Ask a Question
Discussion stats
  • 2 replies
  • 147 views
  • 0 likes
  • 2 in conversation