I am trying to get the earliest start date from a continued series of accounts owned by the same customer. An account ending before any other account started should therefor NOT be counted. The goal is to be able to call the macro as a function from a proc sql like this: I am on SAS 9.2 in an aix environment (my workstation being an ordinary windows laptop) Insert into testtable( custID, dateOfEarliestAccount ) values( 3, macro3( 3, <date of today> ) ); Below macro demonstrates what I need, but it is not a function style macro. It puts the right answer in the log. For it to be a function I should have the "fourth last line" ("%put &inputDate;") changed into simply "&inputDate" or that's how I have understood it. It seems that the problem then is the proc sql. But I need that sql it's the core of the proces. Please can anybody guid me in the right direction? Code and testdata etc: %macro test3(inputCust,inputDate); %let earliestStartDate=&sysdate; proc sql noprint; select min(startDate) into :earliestStartDate separated by ' ' from mivspace.mivcust_acct where custid eq &inputCust and startDate le &inputDate and endDate ge &inputDate; quit; %if "&earliestStartDate" = "." %then %do; %let earliestStartDate=&sysdate; %end; %if &earliestStartDate ge &inputDate %then %put &inputDate; %else %test3(&inputCust,&earliestStartDate); %mend test3; I can call it like this: %test3( 1, 21089 ) AS you can tell, I use the base representation of dates to have it work (21089 is september 27th og 2017). That might not be neccessary but it works, and it's ok. This is my test data: proc sql; drop table mivspace.mivcust; create table mivspace.mivcust( custID int, name varchar( 100 ) ); insert into mivspace.mivcust( custID, name ) values( 1, 'Test Uno Testesen' ); insert into mivspace.mivcust( custID, name ) values( 2, 'Test Dos Testesen' ); insert into mivspace.mivcust( custID, name ) values( 3, 'Test Tres Testesen' ); insert into mivspace.mivcust( custID, name ) values( 4, 'Test Quatro Testesen' ); drop table mivspace.mivacct; create table mivspace.mivacct( acctID int, custID int, saldo numeric, startDate date, endDate date, testfield varchar( 10 ) ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 10, 1, 1000, 18263, 18628, 'c1a10' ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 11, 1, 11000, 18993, 19724, 'c1a11' ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 12, 1, 5000, 19359, 22000, 'c1a12' ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 20, 2, 1000, 18628, 20089, 'c2a20' ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 30, 3, 1000, 20089, 21063, 'c3a30' ); insert into mivspace.mivacct( acctID, custID, saldo, startDate, endDate, testfield ) values( 40, 4, 12000, 18263, 21100, 'c4a40' ); quit; proc sql; select * from mivspace.mivacct; quit; proc sql; drop table mivspace.mivcust_acct; create table mivspace.mivcust_acct as select c.custID, c.name, a.acctID, a.startDate, a.enddate from mivspace.mivcust c join mivspace.mivacct a on c.custID = a.custID order by c.custID, a.startDate; quit;
... View more