I am trying to create a field 'respondent_id' that contains either the s_id, m_id or c_id depending on what activity the user preformed. The data step below works but the issue I'm having is that some users did multiple acts and it is only storing one ID per user. I want the code to just create a new line if the user has more than one activity instead of overwriting the other.
data act.3;
set act.2;
by user_id;
retain user_id s_id m_id c_id Respondent_id;
if user_id and act_id in (28 29) then do;
respondent_id=c_id;
end;
if user_id and act_id in (27) then do;
respondent_id=m_id;
end;
if user_id and act_id in (25) then do;
respondent_id=s_id;
end;
run;
if user_id and ...
treats user_id as a boolean variable which is considered true if not 0.
How is act_id stored? The way you handle it, there cannot be more than one activity per record, so all activities will be output.
The log of the data step might be helpful.
act.3 is not a valid dataset name. This datastep cannot run, contrary to what you are saying. With valid dataset names you might try :
data three;
set two;
if user_id > 0 then do;
select (act_id);
when (28, 29) respondent_id=c_id;
when (27) respondent_id=m_id;
when (25) respondent_id=s_id;
otherwise;
end;
end;
run;
PG
Use an OUTPUT statement to control the output.
I don't know what you're expecting the user_id statement in the IF statement to accomplish. It will evaluate to true if the user_id is greater than 0.
Perhaps something like this:
data act.three;
set act.two;
by user_id;
if user_id and act_id in (28 29) then do;
respondent_id=c_id;
output;
end;
if user_id and act_id in (27) then do;
respondent_id=m_id;
output;
end;
if user_id and act_id in (25) then do;
respondent_id=s_id;
output;
end;
run;
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.