BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
david27
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
%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;

View solution in original post

9 REPLIES 9
Tom
Super User Tom
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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

Reeza
Super User
%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
Quartz | Level 8

Thank You Very Much @Reeza and @Tom.

 

 

Reeza
Super User
Will it always have commas? Or do you need to account for comma's and no commas?
david27
Quartz | Level 8

the macro variable would always have commas

PaigeMiller
Diamond | Level 26

Use

 

%let nos = 9999999;
--
Paige Miller
Ksharp
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 9 replies
  • 4143 views
  • 0 likes
  • 5 in conversation