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
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.