Creating an Identifier Variable

[ndvorak_forrester_com]

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

[Reeza · Accepted solution]

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;