topic Re: keyword quote parameters in SAS Programming

keyword quote parameters

BrahmanandaRao — Fri, 16 Oct 2020 13:04:09 GMT

	%macro psql(dsname= ,x=);
	proc sql;
	select  Name ,sex, Age from &dsname order by &x;
	quit;
 %mend;

	*calling ;
	;
	%psql(dsname=sashelp.class,%str(x=sex, x=age desc));

Re: keyword quote parameters

Jagadishkatam — Fri, 16 Oct 2020 14:08:11 GMT

Please try the below call

%psql(dsname=sashelp.class,x=%nrstr(sex, age desc));

Re: keyword quote parameters

Kurt_Bremser — Fri, 16 Oct 2020 14:19:57 GMT

Since you only need to mask the comma, the %STR function is sufficient:

%psql(dsname=sashelp.class,x=%str(sex,age desc));

Re: keyword quote parameters

BrahmanandaRao — Sat, 17 Oct 2020 07:33:01 GMT

I Did not get only F values its give M and F both

%psql(dsname=sashelp.class,x=%str(sex ='F',Age desc));

Re: keyword quote parameters

Kurt_Bremser — Sat, 17 Oct 2020 08:15:11 GMT

You need to use a WHERE condition in the SQL select.

Start by creating the necessary SQL code without macro coding, once it works, you can make it dynamic.

Re: keyword quote parameters

PaigeMiller — Sat, 17 Oct 2020 18:53:27 GMT

Agreeing with @Kurt_Bremser . You can't write macros and expect them to work until you have working code without macros.

So, here is your code when run as

%psql(dsname=sashelp.class,x=%str(sex ='F',Age desc))

&DSNAME is replaced by sashelp.class and &X is replaced by %str(sex='F',age desc), producing this incorrect SQL code

proc sql;
	select  Name ,sex, Age from sashelp.class order by sex='F',Age desc;
quit;

Can you see why this SQL code doesn't work? So first, you have to write correct SQL code without macros and without macro variables, and fix the problems in the code immediately above. Show us correct SQL code that works, without macros and without macro variables, for this situation.