BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
zcoop
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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,-));

 

View solution in original post

8 REPLIES 8
Quentin
PROC Star

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)
Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Tom
Super User Tom
Super User

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,-));

 

yabwon
Meteorite | Level 14
%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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



zcoop
Fluorite | Level 6
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.
zcoop
Fluorite | Level 6

zcoop_0-1680554751387.png

Looks like a bad url.

yabwon
Meteorite | Level 14

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 188 views
  • 2 likes
  • 4 in conversation