topic Re: Assign -ve numeric value to macro variable in SAS Programming

Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 17:05:10 GMT

Hi All,

I am trying to assign -ve numeric value to a macro variable in SAS symput statement but getting error below.

data one;
infile datalines;
input ID value ;
datalines;
-1 1234
-2 4567
-3 7890
-4 4321
;
run;

data two;
set one;
call symput ( ID,value);
run;

ERROR: Symbolic variable name -1 must begin with a letter or underscore.

Is there a way we can use -ve values without getting any error

Thanks

Kajal

Re: Assign -ve numeric value to macro variable

ChrisNZ — Thu, 19 Sep 2024 17:53:30 GMT

You're using the value as the variable name. Variable names cannot contain a hyphen.

Re: Assign -ve numeric value to macro variable

PaigeMiller — Thu, 19 Sep 2024 17:59:01 GMT

There's no such thing as a macro variable whose name is -1. (Of course, you can have a macro variable with a valid name whose value is -1)

Please explain in words what you are trying to do here.

Re: Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 18:03:30 GMT

I am trying to create macro variable values from ID values whose values are under column 'value'

-1 will have a value 1234

-2 will have a value 4567

-3 will have a value 7890

-4 will have a value 4321

like this

Re: Assign -ve numeric value to macro variable

ChrisNZ — Thu, 19 Sep 2024 18:06:13 GMT

Read the replies again. You can't do that.

Re: Assign -ve numeric value to macro variable

ballardw — Thu, 19 Sep 2024 18:06:40 GMT

How exactly do expect to use that not-legally named variable?

This smells like trying to put way too much data into macro variables.

Re: Assign -ve numeric value to macro variable

ChrisNZ — Thu, 19 Sep 2024 18:14:35 GMT

It sounds like you might want to use formats. What's the final use?

Otherwise you can call the variable something like MINUS1.

Re: Assign -ve numeric value to macro variable

PaigeMiller — Thu, 19 Sep 2024 19:35:01 GMT

@kajal_30 wrote:

I am trying to create macro variable values from ID values whose values are under column 'value'

-1 will have a value 1234

-2 will have a value 4567

-3 will have a value 7890

-4 will have a value 4321

like this

Explain why you want a macro variable to be named -1. Why does it have to be named -1, why can't it be named POMEGRANATE or ELEPHANT? How will this macro variable be used?

Re: Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 19:27:56 GMT

Is it possible to create format in sas without using hardcoded values but values from columns ?

Re: Assign -ve numeric value to macro variable

ballardw — Thu, 19 Sep 2024 19:39:26 GMT

@kajal_30 wrote:

Is it possible to create format in sas without using hardcoded values but values from columns ?

Yes. You have to create a data set with the needed values in the for specific variable names that the procedure expects to see.

data one;
infile datalines;
input ID value ;
datalines;
-1 1234
-2 4567
-3 7890
-4 4321
;
run;

data fmtmaker;
  set one;
  fmtname='Id_to_Value';
  type='N';
  start=Id;
  label=Value;
run;

proc format library=work cntlin=fmtmaker;
run;

proc print data=one;
  var id;
  format id id_to_value.;
run;

There are some elements a bit tricky about ranges of values the Other option in proc format for what to do with not listed values and whether you actually need a FORMAT or an INFORMAT.

Re: Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 20:46:44 GMT

Thanks but it won't resolve my purpose as I need to use those values in a table name for fetching info.

for eg: &var = 2401 then use this value for where table_name = dt_&var. where I can not use format values like this.

Re: Assign -ve numeric value to macro variable

ballardw — Thu, 19 Sep 2024 20:54:34 GMT

That is not a very clear description of how you are attempting to use this.

Any place you have the variable ID you can retrieve the text

Tablename = cats('dt_',put(id, id_to_value.));

And throwing around macro variables with values, where they are assigned or context doesn't clarify much.

Show the values you need, where you need them.

What you start with.

And we may be able to get there.

Assuming 'this won't work' without a clear description of what it is to be used for results in poor suggestions.

And if this related to the https://communities.sas.com/t5/SAS-Programming/Need-help-with-the-code-for-fetching-data-from-different/m-p/944602#M370093 post then why haven't you responded to suggestions there?

Re: Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 21:06:32 GMT

Exactly because I am trying a workaround and as I already clarified that I have values which I need to use to concatenate with other value to create a table name just a simple 1 line ask

have a value -2 associated to another value 1234

so I need to use 1234 in the table name that's it.

Re: Assign -ve numeric value to macro variable

ballardw — Thu, 19 Sep 2024 21:16:34 GMT

@kajal_30 wrote:

Exactly because I am trying a workaround and as I already clarified that I have values which I need to use to concatenate with other value to create a table name just a simple 1 line ask

have a value -2 associated to another value 1234

so I need to use 1234 in the table name that's it.

So, how does this not meet the need, using the format created above:

Tablename = cats('dt_',put(id, id_to_value.));

Re: Assign -ve numeric value to macro variable

kajal_30 — Thu, 19 Sep 2024 21:36:15 GMT

so you are suggesting below ?

Proc sql;
select * from sashelp.car_summary_cats('dt_',put(id, id_to_value.));
quit;

expecting it to resolve as below

Proc sql;
select * from sashelp.car_summary_2412;
quit;

Re: Assign -ve numeric value to macro variable

Tom — Thu, 19 Sep 2024 21:52:21 GMT

@kajal_30 wrote:

so you are suggesting below ?

Proc sql;
select * from sashelp.car_summary_cats('dt_',put(id, id_to_value.));
quit;

expecting it to resolve as below

Proc sql;
select * from sashelp.car_summary_2412;
quit;

Is that the SAS code you are trying to generate?

select * from sashelp.car_summary_2412

What part of that SAS code needs to VARY? Is it just the 2412? If so then put that into a macro variable.

%let mvar=2412;

And then use the macro variable in your code.

select * from sashelp.car_summary_&mvar.

Now where does the 2412 come from? How do you know when to use it?

Re: Assign -ve numeric value to macro variable

ballardw — Fri, 20 Sep 2024 13:51:50 GMT

No, but then I had zero, nada, no idea of how you expected to use the values because you have not shared that.

However you can use a data step to create code to execute that way.

@kajal_30 wrote:

so you are suggesting below ?

Proc sql;
select * from sashelp.car_summary_cats('dt_',put(id, id_to_value.));
quit;

expecting it to resolve as below

Proc sql;
select * from sashelp.car_summary_2412;
quit;

Either of those SQL results are poor as they are sending the output to the results window. Not very useful. If you can build a string with the code such as :

data junk;
   length longstring $ 2000;
   longstring = "Proc sql;
select * from sashelp.car_summary_"||put(-1, id_to_value.)||';quit';
run;

Then you can use Call Execute to use the code in the Longstring, OR write the statements to a text file with a .SAS extension, not needed but helpful later, and %include that file which will treat the contents a code and execute.

If you have a data set with with ID values that might look like:

data _null_;
/* this has values of ID in it*/
   set somedataset; 

   length longstring $ 2000;
   longstring = "Proc sql;
select * from sashelp.car_summary_"||put(id, id_to_value.)||';quit';

   call execute (longstring);
run;

Which would execute that Proc Sql call for every value of ID in the data set.

I would think actual sql would likely have a "Create table " perhaps using that same put(id, id_to_value.) as part of the output data set name

data _null_;
/* this has values of ID in it*/
   set somedataset; 

   length longstring $ 2000;
   longstring = "Proc sql; Create table mylib.output_"||put(id, id_to_value.)||
"as select * from sashelp.car_summary_"||put(id, id_to_value.)||";quit";

   call execute (longstring);
run;

Re: Assign -ve numeric value to macro variable

ChrisNZ — Thu, 19 Sep 2024 22:00:22 GMT

Proc sql;
select * from sashelp.car_summary_cats('dt_',put(id, id_to_value.));
quit;

I suppose ID would be a macro variable for this to be viable? Then you can write something like

select * from sashelp.car_summary_dt_%sysfunc(putn(&id, id_to_value. -l));

Re: Assign -ve numeric value to macro variable

Tom — Thu, 19 Sep 2024 22:06:21 GMT

You need to make valid macro variable names. -1 (or actually the way you ran it 10 spaces followed by -1) does not form a valid macro variable name.

Perhaps you can just convert the hyphen into an underscore? Also no need to make a second dataset if the goal of the step is just to make the macro variables. Use a DATA _NULL_ step.

data _null_;
  set one;
  call symputX('MVAR'||translate(cats(ID),'__','-.'),value);
run;

Let's test it out by running putting them into an otherwise empty symbol table so it is easier to see the results.

data one;
  input ID value ;
datalines;
-1 1234
-2 4567
-3 7890
-4 4321
;

%macro test;
data _null_;
  set one;
  call symputX('MVAR'||translate(cats(ID),'__','-.'),value,'L');
run;
%put _local_ ;
%mend test;
%test;

Result

NOTE: There were 4 observations read from the data set WORK.ONE.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


TEST MVAR_1 1234
TEST MVAR_2 4567
TEST MVAR_3 7890
TEST MVAR_4 4321

But why are you converting ALL of them into macro variables? Can't you just leave the values in the dataset and convert them into macro variables (or perhaps straight into code) when you actually need it?

PS You almost never want to use CALL SYMPUT(). CALL SYMPUTX() has been available since at least version 6 of SAS (over 30 years). The only reason to use CALL SYMPUT() is if you want the macro variables to contain leading and/or trailing spaces. Something I really doubt that want for this application.

Re: Assign -ve numeric value to macro variable

PaigeMiller — Thu, 19 Sep 2024 22:49:04 GMT

@kajal_30 wrote:

Exactly because I am trying a workaround and as I already clarified that I have values which I need to use to concatenate with other value to create a table name just a simple 1 line ask

have a value -2 associated to another value 1234

so I need to use 1234 in the table name that's it.

So why do you need a macro variable named -2? Why not create a lookup table where you look up -2 and then you retrieve the value 1234? All this can be done in a SAS data set, no macros needed.

You are too tightly focused on the need for a macro variable, which as we explained cannot be named -2. We are trying to propose other ways to get you there, but to do that, we need you to step back and explain the problem at a high level, and not at the "I must have a macro variable" level. This focus on a particular piece of coding you want is usually a bad thing, because it prevents us from suggesting better ways to handle the problem. This is called the XY problem and is not a good way to proceed.