data step multiple outputs per observation

[khill]

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

id	cnt_ref_id	ref_id
000ab001	2	x1,x2
000ab002	1	x1
000ab003	3	x2,y1,y2
000ab004	1	x2

Desired output dataset 

_id	_ref_id
000ab001	x1
000ab001	x2
000ab002	x1
000ab003	x2
000ab003	y1
000ab003	y2
000ab004	x2

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

[Reeza · Accepted solution]

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;

[khill]

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;