topic Re: Problem name table with float numbers in SAS Programming

Problem name table with float numbers

mazouz — Fri, 28 Aug 2020 05:39:33 GMT

Hello
I created a macro which has a threshold parameter and which generates a table name: table_&threshold. then I created another macro of iterations of the first macro by threshold but my problem is when I want to use comma thresholds it does not work because the name of the table cannot contain a comma for example: table_0.5
I want to calculate another parameter when I Have threshold 0.5 I want to generate table_0_5

this is part of my code:

%macro macro2(v, threshold);
proc sql; create table_&threshold
....
%mend;

%macro table_result(V,threshold);
%DO i=1 %TO &threshold %BY 0.5;
%macro2(&V,&i);
%END;
%mend;

Re: Problem name table with float numbers

andreas_lds — Fri, 28 Aug 2020 05:38:58 GMT

Having data in names of datasets or variables is a bad idea in 934 of 937 cases.

Maybe the problem you have could be solved by using by-group-processing, that way you don't need to write loops and you can get rid of unnecessary macro-code.

Re: Problem name table with float numbers

mazouz — Fri, 28 Aug 2020 05:44:28 GMT

How I can use by-group-processing?
you can give me an exemple ?

Re: Problem name table with float numbers

Shmuel — Fri, 28 Aug 2020 06:26:58 GMT

There are errors in the code as posted:

1) Macro variable amp was not assigned.

2) create table_&threshold - did you mean: create table_&amp._threshold ?
(I have replaced the ';' by '._' )

3) %TO &threshold - same error as above ?!

4) To convert 0.5 into 0_5 you can use next code:

%let ampx = %sysfunc(translate(&amp,'_','.'));

then use &ampx to generate the table name;

Re: Problem name table with float numbers

Kurt_Bremser — Fri, 28 Aug 2020 06:40:28 GMT

& is a HTML code for the ampersand; it was inserted because code was not posted into a code window.

@mazouz Please do ALWAYS(!) use the "little running man" button (right next to the one indicated):

for posting code.

I guess that the code snippets should be

%macro macro2(v, threshold);
proc sql; create table_&threshold
....
%mend;

%macro table_result(V,threshold);
%DO i=1 %TO &threshold %BY 0.5;
%macro2(&V,&i);
%END;
%mend;

which would, of course, create invalid dataset names. Since we have no idea what the V parameter in macro2 is doing, there's no way to know what is actually the goal of this construct, and how to do it better (and syntactically correct).

To replace dots with underlines, one can use the TRANSLATE function:

%macro table_result(vthreshold);
%do i = 1 %to &threshold. %by 0.5;
  %let i_corr = %sysfunc(translate(&i.,_,.));
  %macro2(&v.,&i_corr.);
%end;
%mend;

Re: Problem name table with float numbers

Astounding — Fri, 28 Aug 2020 10:09:24 GMT

It's been a long time since I tested this, so I could be wrong, but ...

SAS does not permit decimal points in the %DO loop parameters. This in itself would be illegal:

%do i=1 %to 5 %by 0.5;

Instead, you have to use integer values only, such as:

%do i=10 %to 50 %by 5;

To convert your original loop:

%do i=1 %to &amp.threshold %by 0.5;
   %macro2(&amp.V,&amp.i);
%end;

first correct the %END value in the loop (&amp.threshold isn't a valid number) then you could use:

%do i=10 %to 10*&amp.threshold %by 5;
   %macro2(&amp.V,%sysevalf(&i/10));
%end;

You would still need to convert the decimal points to underscores, as others have pointed out. It might be easier to forget about decimal points entirely. Change the definition of %MACRO2 (adding logic to pick apart the value of &i to determine the data set name):

%do i=10 %to 10*&amp.threshold %by 5;
   %macro2(&amp.V, &i)
%end;

Re: Problem name table with float numbers

Ksharp — Fri, 28 Aug 2020 12:10:51 GMT

%macro macro2(v, threshold);
%put &v &threshold table_%sysfunc(translate(&threshold,_,.));
%mend;

%macro table_result(V,threshold);
%let i=1 ;
%do %while(%sysevalf(&i<=&threshold));
%macro2(&V,&i);
%let i=%sysevalf(&i+0.5);
%END;
%mend;

%table_result(1,5)

Re: Problem name table with float numbers

s_lassen — Fri, 28 Aug 2020 13:12:08 GMT

As other have already remarked, macro numbers are always whole numbers. So you would have to do something else to get that number, e.g.

%macro table_result(V,threshold);
  %local i;
  %do i=10 %to &threshold.0 %by 5;
    %macro2(&V,%substr(&i,1,%length(&i)-1).%substr(&i,%length(&i)));
    %end;
%mend;

As you cannot have periods/decimal points in a normal SAS table name, you would have to do something else, one possibility is to use this syntax:

create table "table_&threshold"n

This will work, but you will have to refer to the table in the same way in your program, e.g. 'table_1.5'n