topic Re: How do I select participants' observations based on admission length and representation in a yea in SAS Programming

How do I select participants' observations based on admission length and representation in a year?

ImSame — Mon, 14 Aug 2017 19:23:22 GMT

Hi,

I have the following dataset,

ID	Admission	Start_Year	End_Year

1	1	10/11	11/12
1	2	10/11	12/13
1	3	12/13	15/16

2	1	10/11	11/12
2	2	10/11	12/13
2	3	10/11	15/16

3	1	10/11	12/13
3	2	14/15	15/16

All admissions have a start date and end date in fiscal years (i.e. from April 01st to March 31st). All admissions start in one fiscal year and end in another fiscal year.

I want to include the most encompassing admissions for each individual i.e. the admissions with the most N fiscal years. I also want to include admissions to ensure there is representation from each fiscal year for each individual, even though they may not be the most encompassing admissions.

For example, in the case of ID 3, the most encompassing admission is admission 1. As such, it should be included. However, I also want to include admission 2 because it includes fiscal years 14/15 and 15/16, which are not represented by admission 1.

The desired output should be as follows,

ID	Admission	Start_Year	End_Year	Include

1	1	10/11	11/12	0
1	2	10/11	12/13	1
1	3	12/13	15/16	1

2	1	10/11	11/12	0
2	2	10/11	12/13	0
2	3	10/11	15/16	1

3	1	10/11	12/13	1
3	2	14/15	15/16	1

Any help would be much appreciated.

Thank you!

Re: How do I select participants' observations based on admission length and representation in a yea

PGStats — Tue, 15 Aug 2017 02:08:28 GMT

Try this:

data have;
input ID Adm Start End; 	 	 
datalines;
1	1	10	11
1	2	10	12
1	3	12	15
2	1	10	11
2	2	10	12
2	3	10	15
3	1	10	12
3	2	14	15
;

proc sql;
create table want as
select 
    ID,
    Adm,
    Start,
    end,
    not exists (
        select ID 
        from have 
        where   ID = a.ID and 
                Adm ne a.Adm and 
                Start <= a.start and 
                End >= a.end ) as include
from have as a;
select * from want;
quit;

Re: How do I select participants' observations based on admission length and representation in a yea

ImSame — Tue, 15 Aug 2017 12:47:42 GMT

Hi PGStats,

Thank you for your response.

The code is implementing the desired logic. Can you please explain the steps in the code, so I can get an idea about the implementation for the full dataset?

Thank you once again.

Re: How do I select participants' observations based on admission length and representation in a yea

PGStats — Wed, 16 Aug 2017 05:09:52 GMT

The query sets include = 1 (TRUE) for periods (start-end) that do not have an enclosing period for the same ID. An enclosing period is one that starts before or in the same year and that ends after or on the same year as the period considered.

The query sets include = 0 (FALSE) for periods that do have an enclosing period for the same ID.

Re: How do I select participants' observations based on admission length and representation in a yea

ImSame — Sun, 20 Aug 2017 00:05:36 GMT

Hi PGStats,

Thank you for your response.

I have a larger dataset with other variables as well. I want to execute this logic for only a subset of the data based on other conditions (e.g. males, adults etc.). I tried including an additional “where” statement at the end to specify the additional conditions, but the code does not execute properly. Can you please suggest how to incorporate additional conditions?

Thank you.

****

Example:

proc sql;

create table want as

select

ID,

Adm,

Start,

end,

not exists (

select ID

from have

where ID = a.ID and

Adm ne a.Adm and

Start <= a.start and

End >= a.end ) as include

from have as a;

where (variableA=x) and (variableB=y) and (variable=z)

quit;

Re: How do I select participants' observations based on admission length and representation in a yea

Patrick — Sun, 20 Aug 2017 00:48:21 GMT

@ImSame

Always be specific what's not working i.e. by posting the relevant SAS Log message.

Given the code you've posted: Remove the semicolon after from have as a;

...

from have as a

where (variableA=x) and (variableB=y) and (variable=z)

;

quit;

Re: How do I select participants' observations based on admission length and representation in a yea

ImSame — Sun, 20 Aug 2017 03:54:24 GMT

Hi Patrick,

I just caught a mistake in the earlier post. I had actually executed the code without the semi colon.

There is no error in the log, but the code is not executing properly. It is erroneously marking observations as 1, when they should 0. In the updated code, observations that do not meet the new “where” clause should be denoted as 0.

Thank you.