Data manipulation add vertical a value separated by comma

[melassiri]

Please help with this
how can you transfer data from situation 1 to situation 2?
data situation 1:
column A : A1 = 1 ; A2 = 2 ; A3=3
column B : B1 = 11 ; B2 = 12 ; B3 = 13
column C : C1 = allo , okay ; C2 = ; C3 = z , d , h
data situation 2:
column A : A1 = 1;  A2 = 1;  A3 = 2;  A4 = 3;   A5 = 3;  A6 = 3
column B : B1 = 11; B2 = 11; B3 = 12; B4 = 13; B5 = 13; B6 = 13
column C : C1 = allo;  C2 = okay;  C3 = ;  C4 = z ;  C5 = d;  B6 = h

note that C2 is when situation 1 is empty and C3 is when situation 2 is empty
so data input is situation 1 and data output is situation 2

[ballardw · Accepted solution]

Someone appears to be thinking like a spreadsheet. If A1 is supposed to be the value of variable A in the first observation then you have a data set that looks like:

data have;
   input a b c:$15.;
datalines;
1 11 allo,okay
2 12 .
3 13 z,d,h
;

ure please attempt to provide existing data in the form of a working data step pasted into a text box opened on the forum using the </> icon that appears above the main message window. The text box is important because the forum software will reformat pasted text and may result in code that will not run.

The above assumes none of your C values have spaces in them and a space can be used to delimit the data for reading with list input and assumes that A and B are numeric values since you did not specify in any way whether they are numeric or character.

Such can be done with :

data want (rename=(cword=c));
  set have;
  length cword $ 15;
  words=countw(c,',');
  if words=0 then output;
  else do i=1 to words;
   cword = scan(c,i,',');
   output;
  end;
  drop i words c;
run;

Because we need the entire value of C someplace and you want different values apparently in the output we will need to rename the C at some time. The above code renames the single value variable on the output set to the name of C.

The OUTPUT instruction writes to the output set when encountered. So you can specify conditions and values when using such. In this case we determine of the C value has any elements counting values separated by commas. If none, then write out immediately. Otherwise extract each "word" using the SCAN function and then write out one at a time. The other variable values, A and B are not manipulated so the starting value gets written out repeatedly for value in C.

[melassiri]

thank you for your help. I appreciate that