DATA Step, Macro, Functions and more

data step multiple outputs per observation

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

data step multiple outputs per observation

Hello,

I'm looking for examples of how I can write out multiple outputs per observation. My data is structured in such a way as each obsevration contains 1 to 500 csv values in one field. I'd like to take these obsevations and create a 1n1 relationship table.

Current input dataset

idcnt_ref_idref_id
000ab0012x1,x2
000ab0021x1
000ab0033x2,y1,y2
000ab0041x2

Desired output dataset 

_id_ref_id
000ab001x1
000ab001x2
000ab002x1
000ab003x2
000ab003y1
000ab003y2
000ab004x2


To perfrom this I'm looking at the datastep as a possible solution.This this sort of structure;

data work.output;

SET work.input;

  by id;

IF cnt_ref_id > 0 then

     DO While cnt_ref_id >0

          _id = id

         _ref_id = <code to parse next ref_id>

         cnt_ref_id = cnt_ref_id - 1

;

Run;

Any insight or direction would be appreciated.

Sincerely,

John


Accepted Solutions
Solution
‎10-03-2011 04:49 PM
Super User
Posts: 19,862

data step multiple outputs per observation

If you have the counts look up the scan function in combination with an output statement to get what you want.

I don't think you need the by statement.

data work.output;

SET work.input;

IF cnt_ref_id > 0 then do;

     DO i=1 to cnt_ref_id;

          _id = id;

         _ref_id = scan(ref_id, i, ',');

       output;

    end;*do loop;

end;*if condition;

;

Run;

View solution in original post


All Replies
Solution
‎10-03-2011 04:49 PM
Super User
Posts: 19,862

data step multiple outputs per observation

If you have the counts look up the scan function in combination with an output statement to get what you want.

I don't think you need the by statement.

data work.output;

SET work.input;

IF cnt_ref_id > 0 then do;

     DO i=1 to cnt_ref_id;

          _id = id;

         _ref_id = scan(ref_id, i, ',');

       output;

    end;*do loop;

end;*if condition;

;

Run;

Contributor
Posts: 23

data step multiple outputs per observation

Thank you Reeza. This is exactly what I needed.

SET WORK.ALL_COL;
  BY id;

keep id _ref_id;
delim1 = ';';

do count = 1 to cnt_ref_id;
  _ref_id = scan(re_id,count,delim1, 'or');
  output;
end;

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 161 views
  • 0 likes
  • 2 in conversation