topic Re: How do I create a variable that counts same day observations from different locations by ID in New SAS User

How do I create a variable that counts same day observations from different locations by ID

aokolo — Fri, 26 Aug 2022 20:06:44 GMT

Hello,

I'm a novice SAS user (SAS 9.4) and I'm working with a healthcare dataset where I need to count the number of observations that occurred on the same date at different places of service. Example, if ID 1 was seen at the ED on 01/01/2020 and Inpatient on 01/01/2020 then the new variable would count that as 1 service but if ID 1 was seen at the ED on 01/01/2020 and Inpatient on 01/31/2020 then the new variable wouldn't count that. How would I create a variable that would count that?

data test; input id $ date yymmdd10. place $3. ; format date yymmdd10.; datalines; 1 2020/09/10 ED 1 2020/09/10 IN 2 2020/10/10 IN 3 2020/04/03 ED 3 2020/05/03 ED 3 2020/05/29 IN 4 2020/10/10 IN 4 2020/10/10 ED 4 2020/11/03 ED 4 2020/12/29 IN ; run;

My expected output would be something like this:

ID	DATE	PLACE	NEWVAR
1	9/10/2020	ED	1
1	9/10/2020	IN	1
2	10/10/2020	IN	.
3	4/3/2020	ED	.
3	5/3/2020	ED	.
3	5/29/2020	IN	.
4	10/10/2020	IN	1
4	10/10/2020	ED	1
4	11/3/2020	ED	.
4	12/29/2020	IN	.

I would really appreciate any insight. Thank you!

Amanda

Re: How do I create a variable that counts same day observations from different locations by ID

Reeza — Fri, 26 Aug 2022 17:09:02 GMT

Please show expected output.

Re: How do I create a variable that counts same day observations from different locations by ID

aokolo — Fri, 26 Aug 2022 20:02:42 GMT

Hello,

My expected output would be:

ID	DATE	PLACE	NEWVAR
1	9/10/2020	ED	1
1	9/10/2020	IN	1
2	10/10/2020	IN	.
3	4/3/2020	ED	.
3	5/3/2020	ED	.
3	5/29/2020	IN	.
4	10/10/2020	IN	1
4	10/10/2020	ED	1
4	11/3/2020	ED	.
4	12/29/2020	IN	.

Thanks!

Re: How do I create a variable that counts same day observations from different locations by ID

PeterClemmensen — Fri, 26 Aug 2022 20:14:40 GMT

Try this

proc sql;
   create table want as
   select *
        , (count (distinct place) > 1) as newvar
   from test
   group by ID, date
   ;
quit;

Re: How do I create a variable that counts same day observations from different locations by ID

aokolo — Tue, 30 Aug 2022 15:42:59 GMT

Thank you! This worked.