Issues with Call Execute macro program

[pangea17]

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.

[Tom · Accepted solution]

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;

[pangea17]

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.