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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.