how do I change a Macro into a function like macro?

[MikkelVejlby]

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;

[Kurt_Bremser]

data mivcust_acct;
infile cards dlm='09'x;
input custid name:$50. acctid startdate:date9. enddate:date9.;
format startdate enddate date9.;
cards;
1	Test Uno Testesen	10	01JAN2010	01JAN2011
1	Test Uno Testesen	11	01JAN2012	01JAN2014
1	Test Uno Testesen	12	01JAN2013	26MAR2020
2	Test Dos Testesen	20	01JAN2011	01JAN2015
3	Test Tres Testesen	30	01JAN2015	01SEP2017
4	Test Quatro Testesen	40	01JAN2010	08OCT2017
;
run;

data want (keep=custid name earliest_date);
set mivcust_acct;
by custid;
retain earliest_date;
format earliest_date date9.;
if first.custid or startdate > lag(enddate)
then earliest_date = startdate;
if last.custid then output;
run;

I took the liberty to greatly simplify your example data setup.

[MikkelVejlby]

Thank you 🙂

[ballardw]

Depending on how you intend to use a "function like macro" you probably can't use any Proc or Data step inside the macro. Remember that what a macro does is generate SAS code. So when you attempt somethingl like:

data want;

   set have;

   value = %somemacro(a,b,c);

run;

If the "macro" calls proc sql then it attempts to resolve when compliling the data step to

data want;

    set have;

    value =

    Proc sql;

         select a

         from b

         where c;

    quit;

or similar. So when the compiler sees the resolved Proc sql bit of code you get numerous errors about value = proc sql; is invalid syntax, select is incorrect (for a data step select) and quit is unrecognized.