topic Re: How to recall a macro that holds a numeric value in SAS Programming

How to recall a macro that holds a numeric value

awais — Mon, 30 Sep 2019 03:02:11 GMT

Hi,

I am struggling to pass a numeric value through a sas macro. Program1 generates two macros, prodname and prodcost. Program2 filters the data by using the character value that we have stored through program1- this program is working fine. Program3 is intended to filter the data by using the numeric value that we have stores through program2- this program is not working. details of the programs are provided below.

Program1

proc sql;
select prodname, prodcost
into: prodname, : prodcost
from procsql.products;
quit;

%put &prodname and &prodcost;

When I want to use the macro (prodname) that recalls the character value, works perfectly fine. the following program runs perfectly fine:

Program2

proc sql;
select prodname, prodcost
from procsql.products
where prodname = "&prodname";
quit;

Program3

However, when I use the same program to recall the macro that holds the numeric values, it throws me an error.

proc sql;
select prodname, prodcost
from procsql.products
where prodcost > &prodcost;
quit;

the log shows the following error:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

Any help would be appreciated.

Re: How to recall a macro that holds a numeric value

ed_sas_member — Tue, 01 Oct 2019 14:16:33 GMT

Hi @awais ,

Could you please specify what does the macro variable &prodcost look like when you run: %put &prodcost;

For example, are there some leading / trailing blanks in addition to the stored number?

If so, I would suggest to use the a function like trim(&prodcost) to help SAS convert automatically the character value to a numeric one.

Hope this help!

Re: How to recall a macro that holds a numeric value

PaigeMiller — Tue, 01 Oct 2019 14:29:08 GMT

In addition the what @ed_sas_member said, &prodcost may have many values, such as 2.79 0.66 14.08, since PROCSQL.PRODUCTS probably contains many records, so its not really clear what you are trying to do in the case of many records. So, yes, I agree we need to see the contents of &prodcost.

Re: How to recall a macro that holds a numeric value

andreas_lds — Tue, 01 Oct 2019 14:23:43 GMT

@ed_sas_member wrote:

Hi @awais ,

Could you please specify what does the macro variable &prodcost look like when you run: %put &prodcost;

For example, are there some leading / trailing blanks in addition to the stored number?

If so, I would suggest to use the a function like trim(&prodcost) to help SAS convert automatically the character value to a numeric one.

Hope this help!

The function trim removes trailing whitspace only, all macro variables are always character, but because sas-macros exist to create sas-code, which is text, this is hardly a problem at all.

I fully support your request that @awais needs to show the result of the %put-statement. And, @awais, please use the {i}-button and paste the excerpt of the sas log in that window, so that formatting is preserved.

Re: How to recall a macro that holds a numeric value

Tom — Tue, 01 Oct 2019 14:54:46 GMT

First check that the dataset variable PRODCOST is actually numeric. Then check whether PRODCOST has any FORMAT attached to it.

You can change the SELECT statement to remove the format (or convert to a better format) so that the generated macro variable is in a form that is valid in SAS code. You can change the %PUT statement to make it clearer if there are any leading/trailing spaces in the macro variable's value.

proc sql noprint;
select prodname
     , prodcost format=best32.
 into :prodname trimmed
    , :prodcost trimmed
 from procsql.products
;
quit;

%put prodname=|&prodname| and prodcost=|&prodcost|;

For you character variable you might want to use the QUOTE() function to add the surrounding quotes into the macro variable and then you can remove them from your query that references to macro variable.

proc sql noprint;
select quote(trim(prodname))
     , prodcost format=best32.
 into :prodname trimmed
    , :prodcost trimmed
 from procsql.products
;
quit;

Re: How to recall a macro that holds a numeric value

FreelanceReinh — Tue, 01 Oct 2019 14:55:22 GMT

Hi @awais,

I suspect that there's a format associated with variable prodcost in dataset procsql.products and that you expect SAS to use the internal numeric value in your WHERE statement. But macro variables contain only text, in this case the formatted value of prodcost and there is no way to reconstruct the unformatted value from the macro variable alone. So, the solution would be to use a format (temporarily, in the PROC SQL step populating the macro variable) such that the formatted value is valid as a numeric literal (and, of course, is appropriate in terms of precision).

Example:

data products;
prodname='ABC';
prodcost=123.45;
format prodcost dollar8.2;
run;

proc sql noprint;
select prodname, prodcost format=best12.
into :prodname, :prodcost
from products;
quit;

Re: How to recall a macro that holds a numeric value

awais — Wed, 09 Oct 2019 01:43:42 GMT

Hi thank you for replying. Here is the snapshot from log:

%put &prodname and &prodcost;

Dream Machine and $3,200.00

Re: How to recall a macro that holds a numeric value

awais — Wed, 09 Oct 2019 01:46:51 GMT

Hi,

thank you for replying. here is what I am seeing in the log

%put &prodname and &prodcost;

Dream Machine and $3,200.00

Re: How to recall a macro that holds a numeric value

Patrick — Wed, 09 Oct 2019 06:42:28 GMT

@awais wrote:

Hi,

thank you for replying. here is what I am seeing in the log

%put &prodname and &prodcost;

Dream Machine and $3,200.00

It looks like SAS variable prodcost from your table procsql.products has a permanent format of DOLLARw.d attached.

When creating macro variable &prodcost the formatted value gets used (which dollar sign, comma thousand separator). This then causes the error when you try to pass in this string as a numerical value to SAS (as there you can't use a dollar sign and comma separator unless you use such a string in an input function).

May be just set an explicit format of 32. when creating the macro variable as done in below code sample for macro variable &PROD_COST2

data sample;
  format prod_cost dollar16.2;
  prod_cost=320000;
  output;
  stop;
run;

proc sql;
  select 
    prod_cost, 
    prod_cost format=32.
      into 
        :prod_cost1 trimmed, 
        :prod_cost2 trimmed
  from sample
  ;
quit;

%put &=prod_cost1 &=prod_cost2;

PROD_COST1=$320,000.00 PROD_COST2=320000

Re: How to recall a macro that holds a numeric value

awais — Thu, 10 Oct 2019 03:26:51 GMT

Hi Patric,

yeey. I got it working. you were right it had to do something with format of the macro variable. I modified the code as below and it is working:

proc sql;
select prodname, prodcost format 32.
into: prodname,
: prodcost trimmed
from procsql.products;
quit;
%put &prodname and &prodcost;

proc sql;
select prodname, prodcost
from procsql.products
where prodname = "&prodname";
quit;

/* Resolved now */
proc sql;
select prodname, prodcost
from procsql.products
where prodcost > &prodcost;
quit;