Search code examples
datesassas-macro

Create a Macro variable with dates in SAS

I have two datasets - one with accountid and different dates corresponding to each accountid. The data looks something like this

Accountid Account open_date
1234567 21st Nov 2020
1254268 30 Nov 2020

and the second data is a transaction level daily data which looks something like this which has daily balance for each account ID.

Accountid transaction_date Customer_Bal
1234567 21st Nov 2020 400£
1254267 22 Nov 2020 100£
1254268 22 Nov 2020 50£
1254268 23 Nov 2020 0
1254268 24 Nov 2020 20£
1254268 25 Nov 2020 45£

There are 50 different accounts with different account open dates

I want to create a macro code, which fetches the different accountid's from the first table and then takes the balance from the second table from the account opening date to next ten days.

I have created the macro for one date and then how to create the next ten days from that account open date

proc sql;
select distinct account_open_date into :exec_date from abc 
order by account_open_date;

data _null_;
CALL SYMPUT('run0',put(intnx('day',&run.,0,'s'), yymmddn8.));
CALL SYMPUT('run1',put(intnx('day',&run.,1,'s'), yymmddn8.));
CALL SYMPUT('run2',put(intnx('day',&run.,2,'s'), yymmddn8.));
CALL SYMPUT('run3',put(intnx('day',&run.,3,'s'), yymmddn8.));
CALL SYMPUT('run4',put(intnx('day',&run.,4,'s'), yymmddn8.));
CALL SYMPUT('run5',put(intnx('day',&run.,5,'s'), yymmddn8.));
CALL SYMPUT('run6',put(intnx('day',&run.,6,'s'), yymmddn8.));
CALL SYMPUT('run7',put(intnx('day',&run.,7,'s'), yymmddn8.));
CALL SYMPUT('run8',put(intnx('day',&run.,8,'s'), yymmddn8.));
CALL SYMPUT('run9',put(intnx('day',&run.,9,'s'), yymmddn8.));
CALL SYMPUT('run10',put(intnx('day',&run.,10,'s'), yymmddn8.));
run;

How to i store all the different account_open_dates in macro and then take out the the next ten days transactions from the second table for each accountid

Solution

  • You really don't need macro for this processing.

    A simple SQL join will get everything you need in a single table that can be further processed in PROC or DATA steps that use a BY statement.

    Example:

    Presume date variables contain SAS date values (which are integers for days from SAS date epoch).

    proc sql;
  create table first_ten_days as
  select 
    accounts.account_id
  , accounts.open_date
  , transactions.transaction_date
  , transactions.balance
  from accounts
  join transactions
  on accounts.account_id = transactions.account_id
  where transactions.transaction_date - accounts.open_date <= 10
  order by account_id, transaction_date
  ;

proc ...
  by account_id;
  ...