Proc SQL within macro to do a qualitative count

[leonvdwalt]

Hi all

I'm needing a little help. I want to identify a 'regular' payment from a customer's account (or into) by comparing it with the previous six months' transactions for that customer. What I've come up with so far is:

%macro isregularpayment(subset, account, day_of_month, trns_val, cr = 1, sensitivity_days = 3, sensitivity_values = 0.2, sensitivity_val_min = 100, sensitivity_val_max = 99999999999, num_trns_sensitivity = 4);

%if &cr = 1 %then %let type=CREDIT; %else %let type=DEBIT;
%let numrecords = ;
proc sql;
		select COUNT(*)
		into :&numrecords
		from &subset
		where (&account == accountnumber) AND
				(&sensitivity_val_min < &type < &sensitivity_val_max) AND
				(&day_of_month - &sensitivity_days < monthday < &day_of_month + &sensitivity_days) AND
				(&trns_val * (1 - &sensitivity_values) < &type < &trns_val * (1 + &sensitivity_values));
quit;
%if &numrecords GE &num_trns_sensitivity %then %let isregularpayment = 1; %else %let isregularpayment = 0;
%mend;

Being somewhat inexperienced with macros, this piece of code generates a lot of errors. But I show it here to explain some business rules I want to follow:

A regular payment is defined as (i) having occurred at least 4 times in the previous six months, (ii) within 3 days of the day (of the month) of this transaction; and (iii) has a value of within 20% of the value of this transaction.

I use the following code (in the data step) to do the iteration:

data leon.temp1;
	set leon.sample_latest_trnsctns;
	reg_payment = %isregularpayment(leon.sample_id_reg_payments, accountnumber, monthday, credit);
run;

What I tried to do with this code (to be used inside a datastep), is to cycle through all the transactions of that account for the current month; compare that one transaction to all the transactions of that account in the previous six months that fall within 3 days on either side of the date of this one transaction and within 20% of the value of the transaction; and count the number of occurences in the preceding six month period.

Is this the best way to go about it or is there perhaps a better way? Working with a bank's database, this can get quite time consuming if programmed uneconomically.

 

[klaz2002]

leonvdwalt,

You cant run a proc step inside a data step. The statement PROC will act as the RUN statement in a data step and end that step.

You can get the reg payment info using two methods.
1) Use proq SQL and Join the master data with your dataset and return a value based on a sub select that will let you know if the payment is regular. This is a complicated set of select statements that I must admit will take a while to figure out. (I need to brush up on that SQL syntax.)

2) Have a data step generate a Format that will store the information (reg payment or not) by some ID.
Say that you had customer unique ID's for your data you would access the payment's status by using the PUT function.
ex.
reg_payment = put(accountno, $paymts.);


You can have the format return a YES or even a number 0, 1.

Have your Proc SQL code that you mapped out create a table with all the accounts regular payment info. Make sure that the accountid is unique (one record for every accountid) and sorted. Then you set the format dataset up like this.

ex
data fmt_gen(keep=label start type fmtname );
  set your_data_set end = last;
      
  length
      label   $40
      start   $10
      type    $4
      fmtname $10
      ;
   
      label   = trim(left(put(accountid,8.)));
      start   = reg_payment;
      type    = "n";
      fmtname = "paymts.";
      if last then do;
        label   = '0';
        start   = 'other';
	type    = "n";
	fmtname = "paymts.";
      end;
  run;

This format should return the reg payment amount for every ID. If the id is not in the list the format should return 0.

Klaz