Creating an Identifier Variable

ndvorak_forrester_com — Wed, 19 Feb 2014 20:01:50 GMT

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

2 144

2 223

3 111

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

2 144 1

2 223 1

3 111 0

4 155 0

Thanks for your help!

, N

Re: Creating an Identifier Variable

Reeza — Wed, 19 Feb 2014 20:54:58 GMT

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;

topic Re: Creating an Identifier Variable in SAS Procedures

Creating an Identifier Variable

Re: Creating an Identifier Variable