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.
... View more
