Change Variable Data Type in a Macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Change Variable Data Type in a Macro


Hi all,

I need some help with a macro, simply when it calls on the account numbers to match, it looks like SAS believes they are different data types.  In the original tables (erch & sample) the variables (vertrag) is text?

Code:

%MACRO create_libra_address ();

     /* create table for results  */

     proc sql;

           create table test (

                VERTRAG char(10),

                VKONTO char(12),

                belnr char(15)

                );

     quit;

%MEND create_libra_address;

%MACRO get_libra_address (account_number);

     proc sql;

           insert into test

                (VERTRAG,

                VKONTO,

                belnr)

           SELECT

                anl.VERTRAG,

                anl.VKONT,

                nd.belnr

           FROM monthly.erch anl inner join monthly.DBERCHZ3 nd

                on anl.belnr = nd.belnr

           WHERE

                anl.VERTRAG eq &account_number. 

                ;

     quit;

%MEND get_libra_address;

/*   get accounts from dataset for libra address data    */

%create_libra_address ();

data _null_;

set sample;

call execute('%get_libra_address(account_number=' || vertrag || ')');

run;

The error I return is:

NOTE: CALL EXECUTE generated line.

1         + proc sql;

1         +             insert into test     (VERTRAG,    VKONTO,    belnr)   SELECT    anl.VERTRAG,    anl.VKONT,    nd.belnr  

FROM monthly.erch anl inner join monthly.DBERCHZ3 nd    on anl.belnr = nd.belnr   WHERE    anl.VERTRAG eq 0230792062      ;

ERROR: Expression using equals (=) has components that are of different data types.

Anyone got a suggestion on how to get this code to work?

Many thanks


Accepted Solutions
Solution
‎11-19-2013 08:38 AM
Super Contributor
Posts: 339

Re: Change Variable Data Type in a Macro

You can't achieve your logic with 2 proc executes. You will need to modify the macro (or create a new one) and do all in a single call execute like

%MACRO get_libra_address2 (account_number=, contract_number=);

     proc sql;

           insert into test

                (VERTRAG,

                VKONTO,

                belnr)

           SELECT

                anl.VERTRAG,

                anl.VKONT,

                nd.belnr

           FROM monthly.erch anl inner join monthly.DBERCHZ3 nd

                on anl.belnr = nd.belnr

           WHERE

                anl.VERTRAG eq &account_number. 

                and anl.vkont eq &contract_number.

                ;

     quit;

%MEND get_libra_address2;

/*   get accounts from dataset for libra address data    */

%create_libra_address ();

data _null_;

set sample;

call execute('%get_libra_address2(account_number=' ||quote(vertrag)||',contract_number='||quote(vkonto)||')');

run;

I put everything I added in BOLD

please review the spelling of vkont to make sure the right var name is used with the appropriate datasets since it showed up as vkonto vkont and vknont over your posts!

View solution in original post


All Replies
Super Contributor
Posts: 339

Re: Change Variable Data Type in a Macro

replace

anl.VERTRAG eq &account_number. 

with

anl.VERTRAG eq "&account_number."

double quotes allow ampersands to resolve, single quotes don't.

If you wanted to do the same type of EQ in a data step you'd need to add some length management if there is, for example, zero padding instead of blank space padding for numbers shorter than the 10 char length.

Macro variables are always text as the macro processor is essentially a text parser. However, it parses "text" as code so if you parse the text "28" as code, it comes up as 28 in the code hence when the code executes, the value is considered numeric instead of character. You can always consider using quote to wrap the variable in your data step if the variable there is text as well. It would however retain blank or zero padding in the macro variable.

That is, an alternate approach would be something like

call execute('%get_libra_address(account_number=' || quote(vertrag) || ')');

with the added quote, the macro variable &account_number. would resolve directly as "0230792062" in the code preventing the error

Super Contributor
Posts: 282

Re: Change Variable Data Type in a Macro

Hi,

If vertrag is a character type then try using:

anl.VERTRAG eq "&account_number."

Regards,

Amir.

Occasional Contributor
Posts: 15

Re: Change Variable Data Type in a Macro

Thanks for your help.... your replies resolved the issue!

I have another question relating to this code - I want to add another varible in the "where" clause, that is found in both tables again  (like the "vertrag" varible).

i.e:

WHERE

   anl.VERTRAG eq &account_number.

   and anl.Vknont eq &contract_number.

I will need to add a line the code below, any suggestions?

call execute('%get_libra_address(contract_number=' || quote(vkonto) || ')'); ???

data _null_;

set sample;

     call execute('%get_libra_address(account_number=' || quote(vertrag) || ')');

run;

Thanks

Solution
‎11-19-2013 08:38 AM
Super Contributor
Posts: 339

Re: Change Variable Data Type in a Macro

You can't achieve your logic with 2 proc executes. You will need to modify the macro (or create a new one) and do all in a single call execute like

%MACRO get_libra_address2 (account_number=, contract_number=);

     proc sql;

           insert into test

                (VERTRAG,

                VKONTO,

                belnr)

           SELECT

                anl.VERTRAG,

                anl.VKONT,

                nd.belnr

           FROM monthly.erch anl inner join monthly.DBERCHZ3 nd

                on anl.belnr = nd.belnr

           WHERE

                anl.VERTRAG eq &account_number. 

                and anl.vkont eq &contract_number.

                ;

     quit;

%MEND get_libra_address2;

/*   get accounts from dataset for libra address data    */

%create_libra_address ();

data _null_;

set sample;

call execute('%get_libra_address2(account_number=' ||quote(vertrag)||',contract_number='||quote(vkonto)||')');

run;

I put everything I added in BOLD

please review the spelling of vkont to make sure the right var name is used with the appropriate datasets since it showed up as vkonto vkont and vknont over your posts!

Occasional Contributor
Posts: 15

Re: Change Variable Data Type in a Macro

Thanks for your help!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 374 views
  • 6 likes
  • 3 in conversation