## Creating an Identifier Variable

Solved
Occasional Contributor
Posts: 9

# Creating an Identifier Variable

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

, N

Accepted Solutions
Solution
‎02-19-2014 03:54 PM
Super User
Posts: 23,754

## Re: Creating an Identifier Variable

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;

All Replies
Solution
‎02-19-2014 03:54 PM
Super User
Posts: 23,754

## Re: Creating an Identifier Variable

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;

🔒 This topic is solved and locked.