Remove comma from macro numbers

[david27]

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;

[Tom]

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

[david27]

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.

[Tom]

%SUPERQ() wants the name of the macro variable to quote. You gave it a number instead.

[Reeza · Accepted solution]

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

[david27]

Thank You Very Much @Reeza and @Tom.

[Reeza]

Will it always have commas? Or do you need to account for comma's and no commas?

[david27]

the macro variable would always have commas

[PaigeMiller]

Use

%let nos = 9999999;

[Ksharp]

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;