Hello,
I need help creating an Identifier Variable where the value is either 1 or 0 depending on a certain condition.
Here is my sample dataset:
USER ID DEVICE ID
1 122
1 239
1 239
1 239
2 144
2 144
2 144
2 223
2 223
3 111
3 111
3 111
4 155
4 155
I need to add an additional variable called "BOTHDEVICES" if a user has 2 or more devices = 1 or if there is only data for 1 device = 0. Each device has a unique ID number. In this example, Users 1 and 2 would have BOTHDEVICES values of 1 and users 3 and 4 would have BOTHDEVICES values of 0, which would be repeating as shown below:
USER ID DEVICE ID BOTHDEVICES
1 122 1
1 239 1
1 239 1
1 239 1
2 144 1
2 144 1
2 144 1
2 223 1
2 223 1
3 111 0
3 111 0
3 111 0
4 155 0
4 155 0
Thanks for your help!
, N
Here's a SQL solution, that first counts the number of devices per UserID and then assigns a 1 or 0 accordingly.
proc sql;
create table want as
select a.*, case when b.num_devices=1 then 0
when b.num_devices>1 then 1
else .
end as indicator
from have as a
join (select userID, count(distinct deviceid) as num_devices
from have
group by userID) as b
on a.userid=b.userID;
quit;
Here's a SQL solution, that first counts the number of devices per UserID and then assigns a 1 or 0 accordingly.
proc sql;
create table want as
select a.*, case when b.num_devices=1 then 0
when b.num_devices>1 then 1
else .
end as indicator
from have as a
join (select userID, count(distinct deviceid) as num_devices
from have
group by userID) as b
on a.userid=b.userID;
quit;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.