topic Re: how do I count difference in dates and a counter on that in SAS Programming

how do I count difference in dates and a counter on that

MV04 — Thu, 19 Dec 2019 10:20:07 GMT

Hi All,

I have got a datset as below

%let report_month = %sysfunc(putn(%sysfunc(today()),monyy7.));

%let report_date = %sysfunc(putn(%sysfunc(today()),date9.));

%let report_date_beg = %sysfunc(putn(%sysfunc(intnx(month,"&report_date"d,0,b)),date9.));

%put report_month : &report_month. report_date : &report_date. report_date_beg :&report_date_beg.;

data disp_elg;

infile datalines dlm = "," dsd;

input id $ flag;

rep_month = symget("report_month");

datalines;

1111,1

1211,0

1311,0

1411,1

1511,1

1611,1

1711,1

1811,1

1911,0

2011,0

2111,1

;run;

This dataset gives me flag as 1 and 0 per ID and reporting month .

I need to produce a dataset and retain a history for 15 months where the varibales would be like mentioned in the target dataset

if flag is 1 and count of ipqm1 in last 15 month = 0 then code = ipqm1

else if flag is 1 and count of ipqm1 > 2 in last 10 months then code = ipqm2

else if flag is 1 and code = ipqm1 and diff_month = 1 then code = ipqm2

else if code = ipqm2 and diff_month = 2 then code = ipqm3

else if code = ipqm3 and diff_month = 6 then code = ipqm1

else if (code = ipqm1 and diff_month > 1) or (code = ipqm2 and diff_month > 2) or (code = ipqm3 and diff_month > 6) then code = ipqm1

else "do nothing"

So basically we need to set the code depending on the differnce in months and count of particular code in that month diff and the data needs to be produced on a monthly basis and get that appended to create the history.

Any support would be highly appriciated

Target

ID,code,rep_month

1111,ipqm1,dec2018
1211,ipqm1,dec2018
1311,ipqm1,dec2018
1411,ipqm1,dec2018
1511,ipqm1,dec2018
1611,ipqm1,dec2018
1711,ipqm1,dec2018
1811,ipqm1,dec2018
1911,ipqm1,dec2018
2011,ipqm1,dec2018
2111,ipqm1,dec2018
2211,ipqm1,Jan2019
1411,ipqm2,Jan2019
2311,ipqm1,Jan2019
1211,ipqm2,Jan2019
2411,ipqm1,Jan2019
2511,ipqm1,Jan2019
2611,ipqm1,Jan2019
2711,ipqm1,Jan2019
2811,ipqm1,Jan2019
2911,ipqm1,Jan2019
3011,ipqm1,Jan2019
3111,ipqm1,Feb2019
3211,ipqm1,Feb2019
3311,ipqm1,Feb2019
1311,ipqm3,Feb2019
3411,ipqm1,Feb2019
3511,ipqm1,Feb2019
3611,ipqm1,Feb2019
3711,ipqm1,Feb2019
3811,ipqm1,Feb2019
3911,ipqm1,Feb2019

Re: how do I count difference in dates and a counter on that

Kurt_Bremser — Thu, 19 Dec 2019 10:37:29 GMT

From where do you derive "count of ipqm1"?

I also see no variable code in your dataset, but you use it in your "else if" condition.

Re: how do I count difference in dates and a counter on that

Kurt_Bremser — Thu, 19 Dec 2019 10:38:55 GMT

And please post code with the "little running man" button, so we do not need to fix the empty lines (and other "funnies") caused by posting in the main window.

Post textual data with the {i} button.

Re: how do I count difference in dates and a counter on that

MV04 — Thu, 19 Dec 2019 12:41:02 GMT

Code is what we need to build in the code as what will be the value of code in a given month for a given ID consiering all those if then.

Every month we would receive a dataset as

1111,1

1211,0

1311,0

1411,1

1511,1

1611,1

1711,1

1811,1

1911,0

2011,0

2111,1

and we need to create target dataset as given above

Re: how do I count difference in dates and a counter on that

Kurt_Bremser — Thu, 19 Dec 2019 12:51:32 GMT

You have answered exactly NONE of my questions. Please read my post again.

Re: how do I count difference in dates and a counter on that

MV04 — Thu, 19 Dec 2019 21:12:59 GMT

From where do you derive "count of ipqm1"?

--- we need to derive it in the code as "every time the code =ipqm1 switches from to cde= ipqm2 the value of count is increased to 1so fisrt month say dec2018 when the process started we got account say 1111 as flag "1" and we set the code=ipqm1 then now we need to write a generic program where we need to decide from the next month the value of the code which would depend on the months difference.

I also see no variable code in your dataset, but you use it in your "else if" condition

---Like I mentioned above the code needs to be determined in the program itself and hence would populate in the target dataset.

code value (ipqm1,ipqm2,ipqm3) should be unique per id in a givem month.first time the code value is set in the program as ipqm1 and save it as the first dataset in the history.
here the things need to be considered from second month onwards are:
1. the number of times(count) the code value changed for an id in last 15 months
2. for a fresh month check for an id if value of code in the history dataset is ipqm1 and the difference in the months as in from current month to the month when the value of code is ipqm1 is 1 then set code = ipmq2
otherwise check if the value of code in the history dataset is ipqm2 and the difference of month when the value is ipqm2 from current month is 2 then set code = ipmq3
oterwise if the value of code in the history dataset is ipqm3 and the difference of month when the value is ipqm3 from current month is 6 then set code = ipmq1
otherwise if (the value of code in the history dataset is ipqm1 and month differnce as above > 1) or (the value of code in the history dataset is ipqm2 and month differnce as above > 2) or (the value of code in the history dataset is ipqm3 and month differnce as above > 6) then code = ipqm1

Re: how do I count difference in dates and a counter on that

MV04 — Sat, 21 Dec 2019 11:12:35 GMT

Hi KurtBremser,

Did you get information you were looking for from my response.

Any assistance will be highly appriciated.

Thanks

Re: how do I count difference in dates and a counter on that

Kurt_Bremser — Thu, 09 Jan 2020 08:52:57 GMT

What will be the first code set at the first entry for a given id, depending on flag?

What is the formula for the next entry encountered?