topic Data manipulation issue in SAS Programming

Data manipulation issue

enigma84 — Thu, 03 Nov 2011 19:01:20 GMT

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,

Data manipulation issue

art297 — Thu, 03 Nov 2011 19:16:22 GMT

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;

Data manipulation issue

enigma84 — Thu, 03 Nov 2011 19:23:28 GMT

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

Data manipulation issue

art297 — Thu, 03 Nov 2011 19:37:51 GMT

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

Data manipulation issue

FriedEgg — Thu, 03 Nov 2011 20:30:11 GMT

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;

Data manipulation issue

Linlin — Thu, 03 Nov 2011 20:37:36 GMT

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;

Re: Data manipulation issue

MikeZdeb — Thu, 03 Nov 2011 22:57:40 GMT

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;