Help using Base SAS procedures

Creating an Identifier Variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

Thanks for your help!

, N


Accepted Solutions
Solution
‎02-19-2014 03:54 PM
Grand Advisor
Posts: 16,877

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;

View solution in original post


All Replies
Solution
‎02-19-2014 03:54 PM
Grand Advisor
Posts: 16,877

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 148 views
  • 0 likes
  • 2 in conversation