SAS How to split an observation into multiple rows according to a string?

user_9686037 0
Viewed 305

here is my problem.

I got the following dataset

obs ID      p1  p2  p3  p4  perc
1   120265  .   .   .   25  a t 90%, b r 10%
2   120267  15  15  15  15  c e 80%, a 20%  
3   120269  20  20  20  20  cc

I want to normalize it this way

obs ID      p1  p2  p3  p4  perc
1   120265  .   .   .   25  a t 90% 
2   120265  .   .   .   25  b r 10%
3   120267  15  15  15  15  c e 80%
4   120267  15  15  15  15  a 20%   
5   120269  20  20  20  20  cc

I'll be glad to hear some suggestion! I can perc in multiple columns, but I have no idea how to normalize the dataseT! I'll be glad to receive some tips.

sas normalization string
1 Answers

I've not coded in SAS for a while but this seems to work fine:

data have;
    length ID $8 p1 $2 p2 $2 p3 $2 p4 $2 list $100;
    infile cards dsd dlm='|' truncover;
    input ID p1 p2 p3 p4 list;
    cards;
120265|.|.|.|25|a t 90%, b r 10%
120267|15|15|15|15|c e 80%, a 20%
120269|20|20|20|20|cc
;

data col2rows;
    set have;
    length perc $10;

    do i=1 by 1 until (perc=' ');
        perc=strip(scan(list, i, ','));

        if perc ne ' ' then
            output;
    end;
run;

data want;
    set col2rows (drop=list i);
run;

Which prints out as expected:

enter image description here

user_82919490
Related