Assign -ve numeric value to macro variable

[Tom]

---

@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?

[ballardw]

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;   

[ChrisNZ]

---

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));

[PaigeMiller]

---

@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.

[Tom]

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.