Data manipulation issue

[enigma84]

Hi Guys,

I am trying to convert one dataset into another (example given below)

Input Data

Name	Age	String
John	28	A,BB
Jack	25	M,BM,M4m,44,65
Jill	26

Output Data

Name	Age	String_Old	String_New
John	28	A,BB	A
John	28	A,BB	BB
Jack	25	M,BM,M4m,44,65	M
Jack	25	M,BM,M4m,44,65	BM
Jack	25	M,BM,M4m,44,65	M4m
Jack	25	M,BM,M4m,44,65	44
Jack	25	M,BM,M4m,44,65	65
Jill	26

This is getting too complicated for the SAS skills that i have. Can anyone help solve the same?

Regards,

[art297 · Accepted solution]

You could do it with something like:

data want (drop=i);

  set have;

  i=1;

  if scan(string,i) eq "" then output;

  else do while (scan(string,i) ne "");

    new_string=scan(string,i);

    output;

    i+1;

  end;

run;

[enigma84]

Thanks...the way you did it is so simple...i was trying to complicate it by thinking about counting the commas and then creating variable basis highest number of occurence of comma...phew...thanks again

[art297]

Simple is usually the correct answer.  It's just usually difficult to think of how to simplify!

[FriedEgg]

Basically the same as Art's method:

data foo;

infile cards missover;

input name $ age string $20.;

if missing(string) then output;

  else do i=1 to countw(string);

   new_string=scan(string,i);

   output;

  end;

drop i;

cards;

John 28 A,BB

Jack 25 M,BM,M4m,44,65

Jill 26

;

run;

[Linlin]

another similarly way:

data have;
infile datalines missover;
informat string $40.;
input name$ age string &;
datalines;
John 28 A,BB
Jack 25 M,BM,M4m,44,65
Jill 26
;

data want (drop=i n rename=(string=string_old));
retain name age string string_new;
length string_new $8;
set have ;
n=countw(string);
if n=0 then output;
else do;
   do i=1 to n;
      string_new=scan(string,i);
      output;
   end;
end;
run;
proc print;
run;

[MikeZdeb]

Hi ... a variation on the other postings (without the LENGTH statement, NEW_STRING has a length of 200) ...

data want;

length new_string $5.;

set have;

do _n_=1 to ifn(countw(string), countw(string), 1);

   new_string=scan(string,_n_);

   output;

end;

run;