topic Re: Name a table after a compressed macro variable value in SAS Programming

Name a table after a compressed macro variable value

zcoop — Mon, 03 Apr 2023 19:53:20 GMT

I feel very close to solving this issue on my own... My guess is it has to do with mismatching variable types?

I'm trying to get my code to produce two tables called 'recentAB' and 'recentAC'. (In actual practice, my programlist will be much, much longer, but all substrings of it will have hyphens that I want to not appear in the titles of the datasets.)

%let programlist = A-B A-C;

%macro testloop;
%local i ithprogram nodash;
proc sql;
	%do i = 1 %to %sysfunc(countw(&programlist, ' '));
		%let ithprogram = %scan(&programlist, &i, ' ');
		%let nodash = compress(&ithprogram, '-');
		create table recent_&nodash as
		select distinct t1.id,
			t1.yeardate,
			t1.program,
		from libraryname.dataset t1
		where t1.yeardate >= "2018" and t1.program = "&ithprogram"
		order by t1.yeardate,
			t1.id;
	%end;
quit;
%mend;
%testloop;

I colored the text (in green) where I'm pretty sure my error is occurring. If I were to replace that part with

create table recent&i as

Then the tables 'recent1' and 'recent2' get created (with their contents correctly filtered)... So I'm not sure why recent&i works when recent&nodash does not?

Reason I think it is a type mismatch is that I get ERROR 22-322 when I try running my code.

Re: Name a table after a compressed macro variable value

Quentin — Mon, 03 Apr 2023 20:02:01 GMT

You are close. Instead of giving you a solution, I'd suggest you play around with debugging a simpler version, with some %PUT statements added to see the values of your macro variables.

%macro testloop(programlist=);
%local i ithprogram nodash;

%do i = 1 %to %sysfunc(countw(&programlist, ' '));
	%let ithprogram = %scan(&programlist, &i, ' ');
	%let nodash = compress(&ithprogram, '-');

	%put &=i &=ithprogram &=nodash  ;
%end;
%mend;
%testloop(programlist=A-B A-C)

Re: Name a table after a compressed macro variable value

Tom — Mon, 03 Apr 2023 20:06:01 GMT

You cannot name a dataset

recent_compress(A-B, '-')

Which is what your program is doing.

If you want to use a SAS function in macro code you need to use the %SYSFUNC() macro function to call the function. And there is no need to try to compress the single quotes out of the value because the %SCAN() function results will not include them since you included single quotes in the set of values to use a delimiter characters.

%let ithprogram = %scan(&programlist, &i, ' ');
%let nodash = %sysfunc(compress(&ithprogram,-));

Re: Name a table after a compressed macro variable value

yabwon — Mon, 03 Apr 2023 20:08:52 GMT

%let nodash = %sysfunc(compress(&ithprogram, _, KDA));

it will left only letters digits and underscore(_) from your string.

Your current code produces:

compress(A-B, '-')

as a value of nodash, you can see it adding:

%put &=nodash.;

before "create table".

If you want to use a function to process a macrovariable or proces something in macro language you need to put the function inside %sysfunc(), e.g.

%let = %sysfunc(compbl(A     B)); /* returns `A B` (only 1 space) */

and since macro language has only text was values you don't need quotes. So in you code this:

%sysfunc(countw(&programlist, ' '))

and this

 %scan(&programlist, &i, ' ');

should be:

%sysfunc(countw(&programlist, %str( )));

and

%scan(&programlist, &i, %str( ));

sot the quotes won't be used in separators list.

All the best

Bart

Re: Name a table after a compressed macro variable value

zcoop — Mon, 03 Apr 2023 20:29:42 GMT

Thank you all for the quick response!... I feel kind of silly for not noticing my lack of %sysfunc() on that line despite me trying to debug the dumb thing for about an hour.

Re: Name a table after a compressed macro variable value

yabwon — Mon, 03 Apr 2023 20:39:17 GMT

Remember Maxim 52! (https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068)

Bart

Re: Name a table after a compressed macro variable value

zcoop — Mon, 03 Apr 2023 20:46:18 GMT

Looks like a bad url.

Re: Name a table after a compressed macro variable value

zcoop — Mon, 03 Apr 2023 20:50:16 GMT

https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068

Re: Name a table after a compressed macro variable value

yabwon — Mon, 03 Apr 2023 21:22:38 GMT

it took the last ")" to the link...