BookmarkSubscribeRSS Feed
sas_
Fluorite | Level 6

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

2 REPLIES 2
Linlin
Lapis Lazuli | Level 10

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;

Linlin
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 798 views
  • 0 likes
  • 2 in conversation