get the beginning period of last value

[Nasser_DRMCP]

data have;
input contract periode status $ ;
datalines;
111111 201801 B1
111111 201802 B1
111111 201803 B2
111111 201804 B3
111111 201805 B1
111111 201806 B1
222222 201801 B1
222222 201802 B2
222222 201803 B3
222222 201804 B3
222222 201805 B3
222222 201806 B3
 ;
run;

data want;
input contract periode status $ ;
datalines;
111111 201805 B1
222222 201803 B3
;
run;

hello,

I would like to get the beginning periode of the last value for each contract

thanks in advance for your help

Nasser

[Tommy1]

To clarify, what you want is to look at the Status at the last Period of the Contract. Then using that Contract and Status you want to look back and find the first Period given that the Status has not changed. In your example, for contract 111111, the first time it has the status B1 is in 201801, but because the Status was B3 in 201804 you are not interested until it becomes B1 and that is where you get the date of 201805. Is my understanding correct? 

[novinosrin · Accepted solution]

Hello @Nasser_DRMCP 

data have;
input contract periode status $ ;
datalines;
111111 201801 B1
111111 201802 B1
111111 201803 B2
111111 201804 B3
111111 201805 B1
111111 201806 B1
222222 201801 B1
222222 201802 B2
222222 201803 B3
222222 201804 B3
222222 201805 B3
222222 201806 B3
 ;
run;

data want;
do until(last.contract);
set have;
by contract status notsorted;
if first.status then t=periode;
end;
do until(last.contract);
set have;
by contract status notsorted;
if t=periode then output;
end;
drop t;
run;

[PaigeMiller]

---

@novinosrin wrote:

Hello @Nasser_DRMCP 

data have;
input contract periode status $ ;
datalines;
111111 201801 B1
111111 201802 B1
111111 201803 B2
111111 201804 B3
111111 201805 B1
111111 201806 B1
222222 201801 B1
222222 201802 B2
222222 201803 B3
222222 201804 B3
222222 201805 B3
222222 201806 B3
 ;
run;

data want;
do until(last.contract);
set have;
by contract status notsorted;
if first.status then t=periode;
end;
do until(last.contract);
set have;
by contract status notsorted;
if t=periode then output;
end;
drop t;
run;

---

Obviously, this gives the desired answer, but I'm not understanding the logic here.

If I comment out the second do-loop, I get the desired answer as well. Am I missing something?

data want;
do until(last.contract);
set have;
by contract status notsorted;
if first.status then t=periode;
end;
/*do until(last.contract);*/
/*set have;*/
/*by contract status notsorted;*/
/*if t=periode then output;*/
/*end;*/
/*drop t;*/
run;

[novinosrin]

@PaigeMiller   Sir, you are right. But the problem is

1.The loop doesn't end until it processes one parent by group , so although T will  temporarily hold the correct periode value, we can't have an explicit output for the first of the last sub by group. 

2. More importantly, If we have too many other associated variables for that periode, then that would involve a whole lot gymnastics of  from copying and parking in temp vars even if we use an array, which can make it very tedious

3. So ideally the easy and lazy double by group pass works best 

[PaigeMiller]

Thank you, all good points. Essentially, your code is more robust to potential real-world possibilities and complications, which the small example presented does not include.

[novinosrin]

@PaigeMiller  Sir, Cheers from Bridgeport,CT. Got into Citizens bank . It's been over a month.  Will PM on linkedin or here  later 🙂 While there is this happy career news, I am very broken as my father is very ill 

@ChrisHemedinger   Sir, This thread confirms citizens network works  for sas communities as well 🙂  coz we are SAS citzens first. Thank you for your help. That means a lot.

[Nasser_DRMCP]

many thanks for your quick response

[Nasser_DRMCP]

Hello novinosrin ,

many thanks for your help.

may I ask you a subsidiary question.

how to get the before last ?

thanks in advance

[Nasser_DRMCP]

sorry,

do not take into account my susidiary question.

I can remove from the dataset all periode greater than last periode.

and then I can re apply your suggested solution

sorry

Nasser

[novinosrin]

Hello @Nasser_DRMCP  Should you have no memory constraints, Hash fun

data have;
input contract periode status $ ;
datalines;
111111 201801 B1
111111 201802 B1
111111 201803 B2
111111 201804 B3
111111 201805 B1
111111 201806 B1
222222 201801 B1
222222 201802 B2
222222 201803 B3
222222 201804 B3
222222 201805 B3
222222 201806 B3
 ;
run;

data _null_ ;
if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("contract") ;
   h.definedata ("contract","periode", "status") ;
   h.definedone () ;
end;
do until(last.contract);
set have end=lr;
by contract status notsorted;
if first.status then h.replace();
end;
if lr then h.output(dataset:'want');
run;

[Ksharp]

@novinosrin  gave you right code.if period is tied , could try this.

data have;
input contract periode status $ ;
datalines;
111111 201801 B1
111111 201802 B1
111111 201803 B2
111111 201804 B3
111111 201805 B1
111111 201806 B1
222222 201801 B1
222222 201802 B2
222222 201803 B3
222222 201804 B3
222222 201805 B3
222222 201806 B3
 ;
run;
data want;
do until(last.status);
set have;
by contract status notsorted;
if last.contract then flag=1;
end;
do until(last.status);
set have;
by contract status notsorted;
if first.status and flag then output;
end;
drop flag;
run;

[novinosrin]

@Ksharp   No wonder my mother thinks you are the best and her favorite!

[Ksharp]

That is my great honor of all time .