topic data step multiple outputs per observation in SAS Programming

data step multiple outputs per observation

khill — Mon, 03 Oct 2011 20:40:19 GMT

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

data step multiple outputs per observation

Reeza — Mon, 03 Oct 2011 20:49:05 GMT

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;

data step multiple outputs per observation

khill — Tue, 04 Oct 2011 15:27:13 GMT

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;