topic Re: How does retain work in sas? in SAS Programming

How does retain work in sas?

tampham92 — Wed, 01 Mar 2023 15:21:17 GMT

How can I generate below Flag variable that it restarts at 0 once the new maturity date is set?

Account ID	As_of_Date	Maturity_Date	Flag
1A	4/30/2022	8/1/2022	0
1A	5/31/2022	8/1/2022	0
1A	6/30/2022	8/1/2022	0
1A	7/31/2022	8/1/2022	1
1A	8/31/2022	9/1/2022	0
1A	9/30/2022	9/1/2022	1
1A	10/31/2022	12/1/2022	0
1A	11/30/2022	12/1/2022	1
1A	12/31/2022	1/1/2023	0

Re: How does retain work in sas?

PaigeMiller — Wed, 01 Mar 2023 15:54:49 GMT

Not sure why you are asking about RETAIN, as this doesn't seem to be needed in the problem you talk about.

data want;
    set have;
    by maturity_date;
    if last.maturity_date then flag=1; 
    else flag=0;
run;

Re: How does retain work in sas?

tampham92 — Wed, 01 Mar 2023 15:53:31 GMT

I don't have that Flag variable yet. I want to generate that Flag variable as shown in the dataset.

My original dataset only 3 variables: Account ID, As of Date and Maturity Date.

Thanks.

Re: How does retain work in sas?

PaigeMiller — Wed, 01 Mar 2023 15:55:58 GMT

Does the code I provided work for you?

Best advice: don't specify that you need to use a specific part of SAS (in this case RETAIN), just ask the question and let the responders pick a method that works.

Re: How does retain work in sas?

tampham92 — Wed, 01 Mar 2023 16:40:18 GMT

The code does not work. I tried and it starts dropping some of my observations off the dataset.

The original dataset contains multiple Account IDs sorted by Account ID and As of Date.

Thanks for the advice btw.

Re: How does retain work in sas?

Tom — Wed, 01 Mar 2023 16:42:30 GMT

As to the question in your message body:

Looks like you just want to save the value of the temporary LAST. automatic flag that is created when you use BY statement.

data want;
 set have;
  by accountID Maturity_Date;
  flag=last.Maturity_Date;
run;

As to the question in your Subject line:

Normally when you are creating a new variable in a data step.

x2 = x*x ;

SAS will reset that variable to missing when it starts the next iteration of the data step. What the RETAIN statement does it let you mark the variables that you don't want SAS to reset to missing. So at the start of the next iteration the variable has the value it had at the end of the previous iteration. That is why it is so useful for remembering things or creating accumulators.

data want;
  set have;
   by id ;
   retain running_total;
    if first.id then running_total=0;
    running_total=sum(running_total,amount);
run;

SAS even has a special statement, the sum statement, to make doing accumulators even easier.

data want;
  set have;
   by id ;
   if first.id then running_total=0;
   running_total + amount;
run;

Re: How does retain work in sas?

PaigeMiller — Wed, 01 Mar 2023 16:48:31 GMT

@tampham92 wrote:

The code does not work. I tried and it starts dropping some of my observations off the dataset.

The original dataset contains multiple Account IDs sorted by Account ID and As of Date.

Thanks for the advice btw.

Nothing in my code will cause observations to be deleted. Can you show us the exact code you used, and the output data set? (I see @Tom has provided almost the same code)

Re: How does retain work in sas?

tampham92 — Wed, 01 Mar 2023 18:03:55 GMT

I think when I just sort the data by "maturity date" without accountID, somehow it drops observations.

It works now once I add accountID back to "BY" statement. Thanks a lot for your and @Tom's help.

Re: How does retain work in sas?

PaigeMiller — Wed, 01 Mar 2023 18:06:47 GMT

@tampham92

Tip for next time: if you want code that works over multiple IDs, give example data that has multiple IDs, and state you want code that works on multiple IDs in your original message.