How do I create a flag based on group

[Trishjais]

Hello Friends,

I have an urgent requirement to create a flag based on some variable. My input data looks like this.

Patient_ID	Transaction date	Product_strength	Base_product
Mike	1/1/2018	BREO_100	BREO
Mike	1/1/2018	BREO_200	BREO
Mike	1/12/2018	BREO_100	BREO
Mike	1/12/2018	BREO_200	BREO
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND
Mike	1/18/2018	XOLAIR_150	XOLAIR
Mike	1/18/2018	XOLAIR_75	XOLAIR
Robert	1/19/2018	ADVAIR_100	ADVAIR
Robert	1/19/2018	ADVAIR_250	ADVAIR
Robert	1/19/2018	ADVAIR_500	ADVAIR
Robert	1/19/2018	SYMBICORT_100	SYMBICORT
Robert	1/19/2018	SYMBICORT_200	SYMBICORT
Robert	1/19/2018	XOLAIR_150	XOLAIR
Robert	1/19/2018	XOLAIR_75	XOLAIR

My objective is to create a flag based on patient_id transaction_date and base_prod and increment this flag within by group when base_product changes. The output table should be like below.

Patient_ID	Transaction date	Product_strength	Base_product	Flag
Mike	1/1/2018	BREO_100	BREO	1
Mike	1/1/2018	BREO_200	BREO	1
Mike	1/12/2018	BREO_100	BREO	2
Mike	1/12/2018	BREO_200	BREO	2
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND	3
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND	3
Mike	1/18/2018	XOLAIR_150	XOLAIR	4
Mike	1/18/2018	XOLAIR_75	XOLAIR	4
Robert	1/19/2018	ADVAIR_100	ADVAIR	1
Robert	1/19/2018	ADVAIR_250	ADVAIR	1
Robert	1/19/2018	ADVAIR_500	ADVAIR	1
Robert	1/19/2018	SYMBICORT_100	SYMBICORT	2
Robert	1/19/2018	SYMBICORT_200	SYMBICORT	2
Robert	1/19/2018	XOLAIR_150	XOLAIR	3
Robert	1/19/2018	XOLAIR_75	XOLAIR	3

[novinosrin]

I can't see a change in base_product here and so how did flag increment to 2 here?

Base_product	Flag
Mike	1/1/2018	BREO_100	BREO	1
Mike	1/1/2018	BREO_200	BREO	1
Mike	1/12/2018	BREO_100	BREO	2
Mike	1/12/2018	BREO_200	BREO	2

[Trishjais]

Hello novinosrin,

Thanks for your reply,

Actually transaction dates are different. so flag should also increment as per my requirement ( even if the base_product is same).

[novinosrin · Accepted solution]

data have;
input Patient_ID $	Transactiondate :mmddyy10.	Product_strength :$20.	Base_product : $20.;
format Transactiondate mmddyy10.;
cards;
Mike	1/1/2018	BREO_100	BREO
Mike	1/1/2018	BREO_200	BREO
Mike	1/12/2018	BREO_100	BREO
Mike	1/12/2018	BREO_200	BREO
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND
Mike	1/15/2018	SPIRIVA_HAND	SPIRIVA_HAND
Mike	1/18/2018	XOLAIR_150	XOLAIR
Mike	1/18/2018	XOLAIR_75	XOLAIR
Robert	1/19/2018	ADVAIR_100	ADVAIR
Robert	1/19/2018	ADVAIR_250	ADVAIR
Robert	1/19/2018	ADVAIR_500	ADVAIR
Robert	1/19/2018	SYMBICORT_100	SYMBICORT
Robert	1/19/2018	SYMBICORT_200	SYMBICORT
Robert	1/19/2018	XOLAIR_150	XOLAIR
Robert	1/19/2018	XOLAIR_75	XOLAIR
;

data want;
if _n_=1 then do;
 dcl hash H (dataset:'have') ;
   h.definekey  ('patient_id',' transactiondate','  Base_product') ;
   h.definedone () ;
   end;
set have;
by patient_id;
if first.patient_id then flag=0;
if h.check()=0 then do; flag+1;rc=h.remove();end;
drop rc;
run;

EDIT: To correct a typo from 'THE' to 'THEN '

[Trishjais]

Thanks Much novinosrin !!

This works flawlessly .. 🙂