Hello,
How can I make this work please?
The macro variables nos has comma in its value.
Please advise.
Thanks
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=&nos.
;
quit;
%put &=new_numbers;
%let new_numbers=;
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=input("&nos.", comma32.);
;
quit;
%put &=new_numbers;
Or another way:
%let new_numbers=;
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=input(compress("&nos.", ","), best32.);
;
quit;
%put &=new_numbers;
You could use macro logic to remove the commas from the string.
%sysfunc(compress(%superq(nos),%str(,)))
Or since you seem to be using it as a number you could let SAS convert the string into a number
input("&nos",comma32.)
Changed code to
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=%sysfunc(compress(%superq(&nos.) , %str(,) ) )
;quit;
%put &=new_numbers;
But it does not seem to be working.
36 proc sql noprint;
37 select numbers
38 into: new_numbers
39 from temp
40 where numbers=%sysfunc(compress(%superq(&nos.) , %str(,) ) )
ERROR: Invalid symbolic variable name 9,999,999.
41 ;
_
22
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.
%SUPERQ() wants the name of the macro variable to quote. You gave it a number instead.
%let new_numbers=;
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=input("&nos.", comma32.);
;
quit;
%put &=new_numbers;
Or another way:
%let new_numbers=;
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where numbers=input(compress("&nos.", ","), best32.);
;
quit;
%put &=new_numbers;
the macro variable would always have commas
Use
%let nos = 9999999;
Same logic.
%let new_numbers=;
%let nos = 9,999,999;
data temp;
input numbers;
datalines;
1
2
3
4
9999999
;
run;
proc sql noprint;
select numbers
into: new_numbers
from temp
where strip(put(numbers,comma32.))="&nos.";
;
quit;
%put &new_numbers;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.