Change character variable values to new observations SAS

0
votes

I have a data set similar to this one: 

DATA zzz;
input prop $15. state $; 
datalines;
101|102|103|224  CA
104|105|106      CA
107|108|109      FL
110|111|112|989  FL
; 
run;

I would like to break up the prop variable and put each value as a new observation matched with state. IE, I would like to end up with:

Prop State

Row 1: 101 CA

Row 2: 102 CA

Etc.

I always get confused when dealing with new rows in SAS.

2
sqlinputsassubstr

2 Answers

2
votes

There are many ways to do this; here's one:

DATA zzz;
   length prop $3 state $2;
   keep prop state;
   input prop_txt $15. state $;
   i = 1;
   do while (i ne 0);
      prop = scan(prop_txt,i,'|');
      i = i + 1;
      if prop = ' '
         then i = 0;
         else output;
      end;
   datalines; 
101|102|103|224  CA 
104|105|106      CA 
107|108|109      FL 
110|111|112|989  FL 
run;

Of course, this is based on the data you described. And I made an assumption on the length of the variables you want to keep.

0
votes

On top of Bob's excellent answer, one could also do:

 DATA zzz; 
 input prop $15. state $; 
  do i = 1 to countw( prop, "|" );
   propn = scan( prop, i, '|' );
   output;
  end;
 datalines;
 101|102|103|224  CA
 104|105|106      CA
 107|108|109      FL
 110|111|112|989  FL
 ; 
 run;