DATA Step, Macro, Functions and more

Data manipulation issue

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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: 7,356

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;

View solution in original post


All Replies
Solution
‎11-03-2011 03:16 PM
PROC Star
Posts: 7,356

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 Smiley Happy

PROC Star
Posts: 7,356

Data manipulation issue

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

Trusted Advisor
Posts: 1,300

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 198 views
  • 0 likes
  • 5 in conversation