BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
blue34
Calcite | Level 5

Hi,

 

 

I am trying to write a code for below example of data:

For each time ID of accounts, that have a balance greater than "0" in any one of the two previous months (not current)  how can we create a flag ?

 

 

time idaccount numberbalance
Jan-18550
Feb-18550
Mar-18550
Apr-18550
May-18530
Jun-185.
Jul-185.
Aug-18540
Sep-18534
Jan-18896
Feb-18867
Mar-18878
Apr-18812
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

For the PROC EXPAND solution, I thought what you asked for was OR rather than & the condition(s) are true ... a small tweak.

 

If you don't have PROC EXPAND, it gets a little clumsy.  Here's a possibility:

 

data want;

set have;

by account_number time_id;

if first.account_number then recnum=1;

else recnum + 1;

flag_back1 = ( lag(balance) > 0 ) * (recnum > 1);

flag_back2 = ( lag2(balance) > 0 ) * (recnum > 2);

flag = max(flag_back1, flag_back2);

drop recnum flag_back1 flag_back2;

run;

View solution in original post

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

One way..

 

data have;
informat timeid monyy5.;
format timeid monyy5.;
input timeid accountnumber balance;
datalines;
Jan-18 5 50
Feb-18 5 50
Mar-18 5 50
Apr-18 5 50
May-18 5 30
Jun-18 5 .
Jul-18 5 .
Aug-18 5 40
Sep-18 5 34
Jan-18 8 96
Feb-18 8 67
Mar-18 8 78
Apr-18 8 12
;

proc sort data=have;
   by accountnumber timeid;
run;

proc expand data=have out=temp method=none;
   by accountnumber;
   id timeid;
   convert balance = lag1balance   / transformout=(lag 1);
   convert balance = lag2balance   / transformout=(lag 2);
run;quit;

data want;
   set temp;
   flag=ifn(lag1balance>0 & lag2balance>0, 1, 0);
   drop lag:;
run;
PaigeMiller
Diamond | Level 26

This seems like a very good solution. It uses the BY statement, which ought to mean that it handles the case of the first observation of an accountnumber and the second observation of an accountnumber. This is superior to what I was thinking of using the LAG and LAG2 functions, which doesn't directly handle cases where you have the first or 2nd observation of an accountnumber, and you'd have to program in the proper logic to handle those cases. The only drawback is that not everyone has a license that includes PROC EXPAND.

--
Paige Miller
blue34
Calcite | Level 5

Thank you, if I wanna look at back to 18 months instead of 2 months, do you have another solution using loop?

blue34
Calcite | Level 5

When I run this code, I am getting for 5 account number in Feb 2018 as 0 flag. However, I want to see for this account as 1 since it has Jan-2018 Balance already.

Astounding
PROC Star

For the PROC EXPAND solution, I thought what you asked for was OR rather than & the condition(s) are true ... a small tweak.

 

If you don't have PROC EXPAND, it gets a little clumsy.  Here's a possibility:

 

data want;

set have;

by account_number time_id;

if first.account_number then recnum=1;

else recnum + 1;

flag_back1 = ( lag(balance) > 0 ) * (recnum > 1);

flag_back2 = ( lag2(balance) > 0 ) * (recnum > 2);

flag = max(flag_back1, flag_back2);

drop recnum flag_back1 flag_back2;

run;

blue34
Calcite | Level 5

Thank you very much!!!!!

 

This worked on SAS. I would like to ask one question, if I want to create flag back to 18 months, how can I do this?

Astounding
PROC Star

Well, it's not too hard to add a bunch of statements (the exact number is up to you):

 

flag_back2 = ( lag2(balance) > 0 ) * (recnum > 2);

...

flag_back16 = (lag16(balance) > 0) * (recnum > 16);

 

Then end with:

 

flag = max(of flag_back1-flag_back18);

drop recnum flag_back: ;

 

In general, however, ask questions.  You should understand how a solution works if you want to feel comfortable and safe using it.

blue34
Calcite | Level 5
I just wrote this for 18 months back flag creation, will try this code.


%macro test();

data want;
set account_fdr_hist2;

by account_fdr time_id;

if first.account_fdr then recnum=1;
else recnum + 1;

%do i=1 %to 18;

flag_back&i = (lag&i(balance) <> 0 ) and (recnum > &i);

flag = max(flag_back&i);

/*drop recnum flag_back1 flag_back2;*/

run;

%mend;

%test;
Astounding
PROC Star

Macro language is a good way to generate the statements.  I think you need to add %END to match with %DO.

 

Also decide what to do with negative balances.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 944 views
  • 1 like
  • 4 in conversation