BookmarkSubscribeRSS Feed
ArpitSharma
Fluorite | Level 6

Hello,

 

I want to put a where clause in the sql query if the user passes it in the macro.

If the user does not pass any macro parameter for the where clause then the query should get executed without any filtering.

I dont want to use DSID etc options asI have to apply the query on to the database tables also.

 

I have this but cannot have it working.

%macro isBlank( param ) ;
  %sysevalf( %superq( param ) =, boolean )
%mend ;

%macro nos_obs(
					dsn	=	 	/*Name of SAS Dataset*/
					where_stmt=abc
					);
%global num_obs	;
	%if %isblank(&where_stmt.)=1 %then %do;
	%let where_cls=;
	%end;
	%else %do;
	%let where_cls=&where_stmt.;	
	%end;

	proc sql noprint;
	select 
		count(*)
			into	:num_obs
	from &dsn.
	&where_cls.
	;quit;
	%put num_obs--&num_obs.;
%mend ;

%nos_obs(dsn=sashelp.class,where_stmt=where age in (12,13));

 

8 REPLIES 8
Reeza
Super User

Sure, I think this works but haven't tested it.  

 

Where 1 and &whereClause;
SuryaKiran
Meteorite | Level 14

Hi,

 

Why are you making it very complicated, the following code will work fine. Is there anything thing difference?

OPTIONS SYMBOLGEN MPRINT MLOGIC;
%macro nos_obs(dsn=,where_stmt=);

	proc sql noprint;
	select 
		count(*)
			into	:num_obs
	from &dsn.
	&where_stmt.;
	quit;
	%put num_obs--&num_obs.;
%mend ;

%nos_obs(dsn=sashelp.class,where_stmt=where age in (12,13));

Thanks,
Suryakiran
ArpitSharma
Fluorite | Level 6
It will not work if the user passes where age =12
Reeza
Super User
You're writing the macro. Define the rules.
If WHERE is included use SuryaKiran solution.
SuryaKiran
Meteorite | Level 14

It's works fine for me:

  %nos_obs(dsn=sashelp.class,where_stmt=where age =12);
MLOGIC(NOS_OBS):  Beginning execution.
MLOGIC(NOS_OBS):  Parameter DSN has value sashelp.class
MLOGIC(NOS_OBS):  Parameter WHERE_STMT has value where age =12
MPRINT(NOS_OBS):   proc sql noprint;
SYMBOLGEN:  Macro variable DSN resolves to sashelp.class
SYMBOLGEN:  Macro variable WHERE_STMT resolves to where age =12
MPRINT(NOS_OBS):   select count(*) into :num_obs from sashelp.class where age =12;
MPRINT(NOS_OBS):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MLOGIC(NOS_OBS):  %PUT num_obs--&num_obs.
SYMBOLGEN:  Macro variable NUM_OBS resolves to        5
num_obs--       5
MLOGIC(NOS_OBS):  Ending execution.
Thanks,
Suryakiran
ArpitSharma
Fluorite | Level 6
We might have to include those strings and other such macros that treats it as string. Even if it has an equal to sign. **Where_stmt=where age =12** will give out error. As the number of positional parameter is different than what is defined
SASKiwi
PROC Star

In my experience named parameters are better that positional ones as it makes it more obvious what has to be added. I usually add a %STR function around the text in complex parameters like WHERE statements as that usually prevents most macro parsing problems.  

ballardw
Super User

@ArpitSharma wrote:
We might have to include those strings and other such macros that treats it as string. Even if it has an equal to sign. **Where_stmt=where age =12** will give out error. As the number of positional parameter is different than what is defined

Which version of SAS are you running this on?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3261 views
  • 0 likes
  • 5 in conversation