Solved
New Contributor
Posts: 3

# Data manipulation issue

Hi Guys,

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

Input Data

NameAgeString
John28A,BB
Jack25M,BM,M4m,44,65
Jill26

Output Data

NameAgeString_OldString_New
John28A,BBA
John28A,BBBB
Jack25M,BM,M4m,44,65M
Jack25M,BM,M4m,44,65BM
Jack25M,BM,M4m,44,65M4m
Jack25M,BM,M4m,44,6544
Jack25M,BM,M4m,44,6565
Jill26

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

Regards,

Accepted Solutions
Solution
‎11-03-2011 03:16 PM
PROC Star
Posts: 8,167

## Data manipulation issue

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;

All Replies
Solution
‎11-03-2011 03:16 PM
PROC Star
Posts: 8,167

## Data manipulation issue

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;

New Contributor
Posts: 3

## Data manipulation issue

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

PROC Star
Posts: 8,167

## Data manipulation issue

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

Posts: 1,318

## Data manipulation issue

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;

Super Contributor
Posts: 1,636

## Data manipulation issue

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;

Valued Guide
Posts: 765

## Re: Data manipulation issue

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;

🔒 This topic is solved and locked.