Issues with Call Execute macro program

pangea17 — Tue, 13 Feb 2024 00:56:15 GMT

Hello all. I am using SAS 8.3 64-bit and Enterprise Guide 9.4. I am trying to write a program to pull each record from a file I upload into a SAS session and pull some data based on the account number. It's giving me an error message that "ERROR: Expression using equals (=) has components that are of different data types." This is not the case, as I have used a macro without the data _null_ step and it gives me the correct information.

%MACRO acct(card,name);

proc sql;

create table dimitar as

SELECT t.CreditAccountId, t.CardID, datepart(t.Repo

rtingDa

te) as ReportingDte format date9., t.MerchantDescription, t.MerchantAccount, c.CreditCardNumber, d.billingcycle as Cycle, e.EntryType, y.AlphaCode, y.TransactionCode, t.Amount, p.PricingStrategy,

day(datepart(t.reportingdate)) as RDay

FROM

newdw.factTransaction t

INNER JOIN newdw.dimTransactionType y ON t.dimTransactionTypeID = y.dimTransactionTypeID

INNER JOIN newdw.dimEntryType e ON t.dimEntryTypeID = e.dimEntryTypeID

INNER JOIN newdw.dimBillingCycle d ON t.dimBillingCycleID = d.dimBillingCycleID

INNER JOIN newdw.dimcardIDReference c ON t.cardid = c.cardId

INNER JOIN newdw.dimPricingStrategy p ON t.dimpricingstrategyID=p.dimpricingstrategyID

WHERE

t.CardID in

( select CardID

from newdw.dimcardIDReference

where creditaccountid in

( select creditaccountid

from newdw.dimcardIDReference

where c.CreditCardNumber = &card)

)

ORDER BY

t.ReportingDate;

QUIT;

%MEND;

data _null_;

set newaccts;

call execute('%acct(card='||trim(CreditCardNumber) ||', name='||trim(Surname)||');');

RUN;

The information in the imported file looks like this:

CreditCardNumber	Surname	FirstName
XXXXXXXXXXXXXXXX	Mouse	Mickey
XXXXYYYYXXXXYYYY	Duck	Daffy

If I do this in the following manner it works fine.

%MACRO acct(card,name);

proc sql;

create table dimitar as

SELECT t.CreditAccountId, t.CardID, datepart(t.ReportingDate) as ReportingDte format date9., t.MerchantDescription, t.MerchantAccount,

c.CreditCardNumber, d.billingcycle as Cycle, e.EntryType, y.AlphaCode, y.TransactionCode, t.Amount, p.PricingStrategy,

day(datepart(t.reportingdate)) as RDay

FROM

newdw.factTransaction t

INNER JOIN newdw.dimTransactionType y ON t.dimTransactionTypeID = y.dimTransactionTypeID

INNER JOIN newdw.dimEntryType e ON t.dimEntryTypeID = e.dimEntryTypeID

INNER JOIN newdw.dimBillingCycle d ON t.dimBillingCycleID = d.dimBillingCycleID

INNER JOIN newdw.dimcardIDReference c ON t.cardid = c.cardId

INNER JOIN newdw.dimPricingStrategy p ON t.dimpricingstrategyID=p.dimpricingstrategyID

WHERE

t.CardID in

( select CardID

from newdw.dimcardIDReference

where creditaccountid in

( select creditaccountid

from newdw.dimcardIDReference

where CreditCardNumber = &card)

)

ORDER BY

t.ReportingDate;

QUIT;

%MEND;

%acct ('XXXXXXXXXXXXXXXX',one);

%acct ('XXXXYYYYXXXXYYYY',two);

This would be fine except I have to pull records for 2000 accounts and that seems ridiculous.

Re: Issues with Call Execute macro program

Tom — Tue, 13 Feb 2024 01:22:49 GMT

I do not see any difference. Can you point out what is different?

I do not see where the parameter NAME is used by the macro.

And the macro keeps generating the same output dataset, so calling it twice in a row will cause the second call to replace the dataset generated by the first call.

But other than that the macro you showed should work when called via CALL EXECUTE().

The SAS log will look a lot neater if you prevent the macro from running while the data _null_ step is executing by adding %NRSTR() into the generated code that is passed to the stack.

data _null_;
  set newaccts;
  call execute(cats('%nrstr(%acct)'
      ,'(card=',quote(trim(CreditCardNumber))
      ,',name=',surname
      ,')'
  ));
run;

Re: Issues with Call Execute macro program

pangea17 — Tue, 13 Feb 2024 17:11:17 GMT

I don't see a difference either, but using the call execute gives me the error message and produces nothing. I added your recommendation %nrstr and now the program works. However, the code only returns 205 rows of data for the first observation, while the program without the call execute function brings back 1010 rows of transactions. I don't understand why it would cut the transactions short.

topic Issues with Call Execute macro program in SAS Programming

Issues with Call Execute macro program

Re: Issues with Call Execute macro program

Re: Issues with Call Execute macro program