How to get first instance of a record and also when there is a change in one variable in big dataset

[PrudhviB]

Hi SAS Experts,

I am trying to exclude records with Hold campaign in a month but take non hold campaign, only the first instance of that record. 

current state I use an indicator that specifies "New_Record_for_the_month_ind" to identify the first record of that record key with a filter saying not in HOLD campaign, however this is excluding the records that switch campaigns during the month. 

ideally I want to have those records that switch as well for that month. see below my have and want data sets. 

appreciate your help on this. 

data have;

input CALENDAR_DATE :datetime20.
(CALENDAR_YR_MTH Record_key	Record_status
Campaign) ($)

New_Record_for_the_month_ind Record_leave_job_ind
;
format CALENDAR_DATE datetime20.;

cards;
09Dec2021:00:00:00 2021-12 77336 ACTIVE HOLD 1 0
10Dec2021:00:00:00 2021-12 77336 ACTIVE HOLD 0 0
11Dec2021:00:00:00 2021-12 77336 ACTIVE HIGH 0 0
12Dec2021:00:00:00 2021-12 77336 ACTIVE HIGH 0 0
13Dec2021:00:00:00 2021-12 77336 ACTIVE HIGH 0 0
14Dec2021:00:00:00 2021-12 77336 ACTIVE HIGH 0 0
07Jan2022:00:00:00 2021-12 90222 INACTIVE HIGH 0 1
08Jan2022:00:00:00 2022-01 17724 ACTIVE HOLD 1 0
09Jan2022:00:00:00 2022-01 17724 ACTIVE HOLD 0 0
10Jan2022:00:00:00 2022-01 17724 ACTIVE HOLD 0 0
11Jan2022:00:00:00 2022-01 17724 ACTIVE HOLD 0 0
12Jan2022:00:00:00 2022-01 17724 ACTIVE HOLD 0 0
07Apr2022:00:00:00 2022-01 08906 INACTIVE HOLD 0 1
08Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 1 0
09Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 0 0
10Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 0 0
11Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 0 0
12Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 0 0
13Apr2022:00:00:00 2022-04 53069 ACTIVE HOLD 0 0
14Apr2022:00:00:00 2022-04 53069 ACTIVE HIGH 0 0
08Jun2022:00:00:00 2022-04 78474 INACTIVE HIGH 0 1
09Jun2022:00:00:00 2022-06 67860 ACTIVE HOLD 1 0
10Jun2022:00:00:00 2022-06 67860 ACTIVE HOLD 0 0
11Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
12Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
13Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
14Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
15Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
16Jun2022:00:00:00 2022-06 67860 ACTIVE HOLD 0 0
17Jun2022:00:00:00 2022-06 67860 ACTIVE HOLD 0 0
08Jul2022:00:00:00 2022-06 01380 INACTIVE HOLD 0 1
09Jul2022:00:00:00 2022-07 37031 ACTIVE HOLD 1 0
10Jul2022:00:00:00 2022-07 37031 ACTIVE HOLD 0 0
11Jul2022:00:00:00 2022-07 37031 ACTIVE HOLD 0 0
12Jul2022:00:00:00 2022-07 37031 ACTIVE HOLD 0 0
13Jul2022:00:00:00 2022-07 37031 ACTIVE MED 0 0
14Jul2022:00:00:00 2022-07 37031 ACTIVE MED 0 0
15Jul2022:00:00:00 2022-07 37031 ACTIVE MED 0 0
16Jul2022:00:00:00 2022-07 37031 ACTIVE MED 0 0

;

data want;


input CALENDAR_DATE :datetime20.
(CALENDAR_YR_MTH Record_key	Record_status
Campaign) ($)

New_Record_for_the_month_ind Record_leave_job_ind
;
format CALENDAR_DATE datetime20.;

cards;
11Dec2021:00:00:00 2021-12 77336 ACTIVE HIGH 0 0
14Apr2022:00:00:00 2022-04 53069 ACTIVE HIGH 0 0
11Jun2022:00:00:00 2022-06 67860 ACTIVE HIGH 0 0
13Jul2022:00:00:00 2022-07 37031 ACTIVE MED 0 0
;

[ballardw]

This seems to work for the example data. More complex data may be questionable.

data want;
   set have;
   where Record_status='ACTIVE' and campaign ne 'HOLD';
   by Record_key notsorted;
   if first.record_key;
run;

The Where removes the records I understand you don't want to consider at all.

The By statement assumes that the data is grouped by that variable and it seems to be a "campaign" identifier or similar.

I have to assume the calendar date will be in chronological order. If not you would have to sort the data such that each "campaign" is together by chronological order to get "first".

When you use a BY statement then SAS creates automatic variables First.<variable> and Last.<variable> that are 1/0 values and can be used to check whether a specific observation is the first or last of a specific by group. The IF in this case is a subsetting if to keep only records that are first of a by group.

The actual Campaign variable was a bit confusing to me when you talk about "switching campaign" but then the variable Campaign did not hold what I thought should be an identifier from the question test. It seems like a "campaign status" indicator. I am guessing that Record_key might actually identify a campaign from the way your output data relates to the question text.

[russt_sas · Accepted solution]

Here is one way to accomplish what you are asking:

proc sort;
by calendar_yr_mth;
run;

data want;
set have;
by calendar_yr_mth;
retain flag;
if first.calendar_yr_mth then flag=0;
lag_cal=lag(calendar_yr_mth);
if flag=0 and calendar_yr_mth=lag_cal and campaign ne 'HOLD' then do;
output;
flag=1;
end;
run;

proc print;
run;