topic Updating WHERE clause in a data step and losing records in SAS Programming

Updating WHERE clause in a data step and losing records

zmalone91 — Thu, 21 May 2020 07:46:18 GMT

I am trying to automate a claims pull process where I assign a time period based on macro date variables. When I run want_R12_prev and want_R12_curr as data steps by themselves, I get the desired number of records for each time period.

data want_R12_prev;
set claims_data;
By var1 var2 var3;
where datepart(Dt_Activity) <= &PrevRunOut.;
if datepart(Dt_Incurred) >= &FromDtPrev. 
     And datepart(Dt_Incurred) <= &ToDtPrev.
     And datepart(Dt_Activity) <= &PrevRunOut. 
     And last.ClmLn Then Rolling12 = "Rolling 12 - 1YrP";
run;

data want_R12_curr;
set claims_data;
By var1 var2 var3;
where datepart(Dt_Activity) <= &CurrentRunOut.;
if datepart(Dt_Incurred) >= &FromDtCurrent. 
     And datepart(Dt_Incurred) <= &ToDtCurrent.
     And datepart(Dt_Activity) <= &CurrentRunOut. 
     And last.ClmLn Then Rolling12 = "Rolling 12 - CYrP";
run;

But when I try to run them together in one data step, want_multiple, it does not bring back all the records the individual data steps bring back. I am attempting to assign a YTD period, then invoke the Where clause twice to assign two different Rolling12 periods. The Where does not appear to do anything as I get the same number of records back as I do without it in the code. The actual code has many more period assignments so instead of creating and joining 30+ different data steps I was wondering if my goal of assigning multiple types of time periods is possible to do in one step?

data want_multiple;
set claims_data;
By var1 var2 var3;

if datepart(Dt_Incurred) >= &FromDt2019. 
	And datepart(Dt_Incurred) <= &ToDt2019.
	And datepart(Dt_Activity) <= &ToDt2019.
	And last.Clmln Then Year = "Year to Date - 1YrP";
else if datepart(Dt_Incurred) >= &FromDt2020. 
	And datepart(Dt_Incurred) <= &ToDt2020. 
	And datepart(Dt_Activity) <= &ToDt2020.
	And last.Clmln Then Year = "Year to Date - CYr";

where datepart(Dt_Activity) <= &PrevRunOut.;
if datepart(Dt_Incurred) >= &FromDtPrev. 
     And datepart(Dt_Incurred) <= &ToDtPrev.
     And datepart(Dt_Activity) <= &PrevRunOut. 
     And last.ClmLn Then Rolling12 = "Rolling 12 - 1YrP";

where datepart(Dt_Activity) <= &CurrentRunOut.;
if datepart(Dt_Incurred) >= &FromDtCurrent. 
     And datepart(Dt_Incurred) <= &ToDtCurrent.
     And datepart(Dt_Activity) <= &CurrentRunOut. 
     And last.ClmLn Then Rolling12 = "Rolling 12 - CYrP";
run;

Big thanks and any input is greatly appreciated!

Re: Updating WHERE clause in a data step and losing records

Astounding — Thu, 21 May 2020 08:54:32 GMT

When the DATA step uses multiple WHERE statements, each one replaces any earlier WHERE statement. Only the last one has any impact.

Since your IF THEN conditions repeat the WHERE conditions, just remove all WHERE statements. Then add at the end of the data step:

if rolling12 ne " " ;

Re: Updating WHERE clause in a data step and losing records

Tom — Thu, 21 May 2020 11:55:16 GMT

The WHERE clause limits the records passed from the source dataset into the data step. The main effect it might have on the logic of your example code is its impact on the LAST. variable you are referencing. If a group has 10 records in your source but the last 2 do not pass the where clause test then the 8th record is the LAST instead of the 10th record.

You cannot apply two independent where clauses in the same data step without reading the source dataset twice. You can augment the WHERE clause by using a WHERE ALSO statement, but that is just the same as writing one WHERE statement with both conditions combine with AND operator.

You might be able to do some more complex calculations in a single data step, but you would need to explain more clearly what you are tying to do. For example if you want to aggregate over a patient (find the min vlaue or max value or count something) but use different criteria for which records to include in each aggregate you could.