topic Re: get the beginning periode of last value in New SAS User

get the beginning period of last value

Nasser_DRMCP — Mon, 01 Jul 2019 19:09:40 GMT

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

Re: get the beginning periode of last value

Tommy1 — Mon, 01 Jul 2019 17:09:12 GMT

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?

Re: get the beginning periode of last value

novinosrin — Mon, 01 Jul 2019 18:04:38 GMT

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;

Re: get the beginning periode of last value

PaigeMiller — Mon, 01 Jul 2019 18:12:03 GMT

@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;

Re: get the beginning periode of last value

novinosrin — Mon, 01 Jul 2019 18:51:51 GMT

@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

Re: get the beginning periode of last value

PaigeMiller — Mon, 01 Jul 2019 18:53:32 GMT

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.

Re: get the beginning periode of last value

novinosrin — Mon, 01 Jul 2019 19:07:35 GMT

@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.

Re: get the beginning period of last value

novinosrin — Mon, 01 Jul 2019 20:03:47 GMT

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;

Re: get the beginning periode of last value

Nasser_DRMCP — Tue, 02 Jul 2019 07:00:06 GMT

many thanks for your quick response

Re: get the beginning periode of last value

Nasser_DRMCP — Tue, 02 Jul 2019 09:18:36 GMT

Hello novinosrin ,

many thanks for your help.

may I ask you a subsidiary question.

how to get the before last ?

thanks in advance

Re: get the beginning periode of last value

Nasser_DRMCP — Tue, 02 Jul 2019 09:34:30 GMT

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

Re: get the beginning period of last value

Ksharp — Tue, 02 Jul 2019 11:56:06 GMT

@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;

Re: get the beginning period of last value

novinosrin — Tue, 02 Jul 2019 13:01:50 GMT

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

Re: get the beginning period of last value

Ksharp — Tue, 02 Jul 2019 13:37:22 GMT

That is my great honor of all time .