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;
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 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.