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 |
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 '
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 |
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).
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 '
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: