BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
pangea17
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Quartz | Level 8

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. 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 239 views
  • 0 likes
  • 2 in conversation