topic MACRO SAS DO loop in SAS Programming

MACRO SAS DO loop

tampham92 — Thu, 12 Apr 2018 21:31:31 GMT

For example, I have 3 months of data stacking like this, and this is what I want to ask. I want to create a new data set based on this one but in which I want to delete accounts having balance constantly below 100 threshold since month 1 and forward. And I would like to repeat the same process for individual month #2, #3 until the end of of my data set. With that purpose, the code should only delete account 1D, 2A and 2D for month 1 and account 4A for month 2. This is just an example represented what I want to do. My actual data is way bigger than this. It has at least 50 months of data so I am looking for a MACRO SAS DO loop that can help me perform what I would like to. Thanks a lot.

Account Number	Month	Balance
1A	1	90
1B	1	500
1C	1	600
1D	1	80
2A	1	300
2B	1	200
2C	1	75
2D	1	780
3A	1	650
3B	1	90
3C	1	336
3D	1	296
1A	2	250
1B	2	525
1C	2	630
1D	2	60
2A	2	90
2B	2	95
2C	2	125
2D	2	80
3A	2	300
3B	2	145
3C	2	300
3D	2	128
4A	2	125
4B	2	150
1A	3	45
1B	3	130
1C	3	150
1D	3	50
2A	3	50
2B	3	157
2C	3	100
2D	3	60
3A	3	150
3B	3	300
3C	3	145
3D	3	530
4A	3	90
4B	3	115

Re: MACRO SAS DO loop

PaigeMiller — Thu, 12 Apr 2018 21:51:46 GMT

No macro needed. Something like this should work

/* UNTESTED CODE */

proc summary data=have;
    class account_number;
    var balance;
    output out=maxbal max=max_balance;
run;
proc sort data=have;
    by account_number;
run;
 /* Delete any account_number that has max_balance < 100 */
data want;
    merge have maxbal;
    by account_number;
    if max_balance<100 then delete;
run;

Re: MACRO SAS DO loop

Kurt_Bremser — Fri, 13 Apr 2018 05:44:38 GMT

Your rule does not make sense. Why would you delete A4 for month 2, when it has a balance of 125, which is > 100 AFAIK?

Only account 1D satisfies your rule for deleting.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 12:47:36 GMT

4A for month 2 before in month 3, that account dropped below 100 already, so I want to delete it.

Re: MACRO SAS DO loop

Kurt_Bremser — Fri, 13 Apr 2018 13:42:50 GMT

That's not your rule:

I quote from your initial post:

delete accounts having balance constantly below 100 threshold since month 1

(emphasis by me)

Please make up your mind and post a clear and correct rule.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 13:57:17 GMT

Sorry If I am not that clear. What I want is that for month 1, I want to delete all accounts having balance constantly below 100 since month 1. But then in month 2, I want to delete all accounts having balance constantly below 100 since month 2, etc. Is it clear now?

Re: MACRO SAS DO loop

Kurt_Bremser — Fri, 13 Apr 2018 14:02:34 GMT

No. 4A is not constantly below 100, as it has a value of 125 in month 2.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 14:14:55 GMT

Sorry I do apologize for that example. but pretty much this is what I wanna do. For individual snapshot, I want to delete accounts that staying below 100 since that particular snapshot

Re: MACRO SAS DO loop

PaigeMiller — Fri, 13 Apr 2018 14:18:43 GMT

So, apparently I have misunderstood the requirements, and so the code I gave is not correct.

let me make sure I have the requirements understood properly now.

You want to delete accounts where the values are always under 100 after the first month that the account appears. The first month the account appears could be over 100, and we still delete the account if all subsequent months are under 100.

Is that a correct understanding?

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 14:40:02 GMT

I want to delete accounts where the values are always under 100 after the month its value starts dropping below 100. So for account 4A example, since its value is above 100 in month 2, it should be kept in month 2 snapshot. But if its value keeps staying below 100 since month 3 (where its value dropped below 100), it should be deleted from the dataset since month 3 snapshot and in later subsequent months. So in your final data, you still see account 4A in month 2, but not in month 3 and forward. Hopefully this is clear. Please let me know if I need to clarify it more.

Re: MACRO SAS DO loop

PaigeMiller — Fri, 13 Apr 2018 14:48:19 GMT

I want to delete accounts where the values are always under 100 after the month its value starts dropping below 100.

This is different than what I thought and different than previous explanations. So, if we have a longer time series and a certain account has values

120

100

we would include this account in month 1, include this account in month 2, include this account in month 3, but not include the account after month 3.

If the sequence of data is

120

100

120

then the account is always included. Is that right?

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 15:05:55 GMT

I will try my best here. For your example,

120

100

For give those value in month #1, if the last 4 account have values going back above 100 in month #5, I want to keep them in month 1, month 2, #3, #4 and #5 as long as its value above 100. but then if one of those starts dropping below 100 (for example in month #6) and constantly stay below until the end of series, then it should be deleted since month #6. Is that clear?

Re: MACRO SAS DO loop

PaigeMiller — Fri, 13 Apr 2018 15:10:04 GMT

Not clear at all.

Why don't we delete the account at month 4? All the values are below 100 at month 4 and beyond.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 15:16:09 GMT

but if I do that, it may delete some accounts that may go above 100 in later months. cause if it goes above 100 in later months, I dont want to delete them in current months unless its value is always below 100 since current month.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 15:21:07 GMT

cause I want to model attrition rate. We dont care about accounts with balance below 100 dollars. But if we just simply deleted account with below 100 for each particular month, it will overstate attrition rate for that month. Because when accounts dropped below 100, it does not actually meant it leaves the bank. But right now by deleting those, it is pretty much I assume they leave the bank for that month (account balance < 100) but actually they do not.

Re: MACRO SAS DO loop

PaigeMiller — Fri, 13 Apr 2018 15:23:28 GMT

You know, at this point I'm still not clear on what you want, so I hope someone else can jump in and give you an answer.

Re: MACRO SAS DO loop

tampham92 — Fri, 13 Apr 2018 15:26:10 GMT

Thank a lot for your help anyway.

Re: MACRO SAS DO loop

Kurt_Bremser — Mon, 16 Apr 2018 07:51:20 GMT

I think I finally get what you want. Try this code:

data have;
input account_nr $ month balance;
cards;
1A 1 90 
1B 1 500 
1C 1 600 
1D 1 80 
2A 1 300 
2B 1 200 
2C 1 75 
2D 1 780 
3A 1 650 
3B 1 90 
3C 1 336 
3D 1 296 
1A 2 250 
1B 2 525 
1C 2 630 
1D 2 60 
2A 2 90 
2B 2 95 
2C 2 125 
2D 2 80 
3A 2 300 
3B 2 145 
3C 2 300 
3D 2 128 
4A 2 125 
4B 2 150 
1A 3 45 
1B 3 130 
1C 3 150 
1D 3 50 
2A 3 50 
2B 3 157 
2C 3 100 
2D 3 60 
3A 3 150 
3B 3 300 
3C 3 145 
3D 3 530 
4A 3 90 
4B 3 115 
;
run;

proc sort data=have;
by account_nr month;
run;

/* set up a dataset that marks accounts for deletion,
   and the month where the deletion should start */
data deletes (keep=account_nr startmonth);
set have;
by account_nr;
retain flag startmonth;
if first.account_nr then flag = 0;
if balance < 100
then do;
  if not flag then startmonth = month;
  flag = 1;
end;
else flag = 0; /* reset when balance >= 100 */
if last.account_nr and flag then output;
run;

data want (drop=startmonth);
merge
  have (in=a)
  deletes (in=b)
;
by account_nr;
if a;
if not b or month < startmonth; /* no delete at all, or startmonth not yet reached */
run;

/* restore original order */
proc sort data=want;
by month account_nr;
run;

Note how I presented your original example data. Posting data in a data step allows people to easily and exactly recreate your example data.

Re: MACRO SAS DO loop

tampham92 — Mon, 23 Apr 2018 16:09:23 GMT

Thank you so much for your help. This is exactly what I want. I really appreciate that. You are such a genius

Re: MACRO SAS DO loop

Kurt_Bremser — Tue, 24 Apr 2018 06:35:32 GMT

Then please mark the post that did it as the solution.