topic Re: Remove comma from macro numbers in New SAS User

Remove comma from macro numbers

david27 — Tue, 20 Nov 2018 17:08:10 GMT

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;

Re: Remove comma from macro numbers

Tom — Tue, 20 Nov 2018 17:26:22 GMT

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

Re: Remove comma from macro numbers

Reeza — Tue, 20 Nov 2018 17:18:30 GMT

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

Re: Remove comma from macro numbers

david27 — Tue, 20 Nov 2018 17:25:51 GMT

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.

Re: Remove comma from macro numbers

Tom — Tue, 20 Nov 2018 17:27:55 GMT

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

Re: Remove comma from macro numbers

Reeza — Tue, 20 Nov 2018 17:29:36 GMT

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

Re: Remove comma from macro numbers

david27 — Tue, 20 Nov 2018 17:33:19 GMT

the macro variable would always have commas

Re: Remove comma from macro numbers

david27 — Tue, 20 Nov 2018 17:43:38 GMT

Thank You Very Much @Reeza and @Tom.

Re: Remove comma from macro numbers

PaigeMiller — Tue, 20 Nov 2018 17:51:07 GMT

Use

%let nos = 9999999;

Re: Remove comma from macro numbers

Ksharp — Wed, 21 Nov 2018 13:16:58 GMT

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;