topic Re: Macro Variables in Proc sql in New SAS User

Macro Variables in Proc sql

ashc25901 — Sat, 24 Nov 2018 23:56:49 GMT

I have this macro cust. In proc sql I need to select only those variables that are listed in vars.

can anyone help for example I have a dataset with variables Acct_ID, Name, Age, Age_Range, Balance, State.

so if I write

%cust(dsn = datasetname, vars = Acct_ID Age Balance State, age_range= "18-40, state = "NY");

Proc sql should also give same selected variable given in macro call.

%macro cust(dsn = , vars = , age_range= , state=);
%if &state = "NY" %then
%do;
    proc print data = &dsn;
    var &vars;
    where State = &state and Age_Range = &age_range;
    title "Detail Listing of Account in &state and current date &sysdate";
    run;

    proc sql;
    title "Total Balance in &state and in age &age_range current date &sysdate";
    select &vars, sum(Balance) as Total_Balance

/* The line above can I write &vars like this because it is throwing error if I am writing like this. */
    from &dsn
    where State = &state and Age_Range = &age_range;
    quit;

%end;

%mend cust;

Thanks.

Re: Macro Variables in Proc sql

Shmuel — Sun, 25 Nov 2018 06:10:35 GMT

1) Move the title line before (out from) the proc sql line.

2) SELECT statement in proc sql requires vars to be separated by commas.

I have added a macro list2sql to add the commas required:

%macro list2sql(var=);
 %let m = %sysfunc(countw("&var"));
 %if %eval(&m) > 0 
 %then 
 %let sqlvar = %scan(&var,1);
 %if %eval(&m) > 1 %then
 %do i=2 %to &m; 
 %let sqlvar = &sqlvar,%scan(&var,&i);
 %end;
 %put sqlvar= &sqlvar;
%mend list2sq;
/*** test list2sql
%list2sql(var=);
%list2sql(var=v1);
%list2sql(var=v1 v2 v3 v4);
******/

%macro cust(dsn = , vars = , age_range= , state=); 
   %if &state = "NY" %then %do; 
       proc print data = &dsn; 
	   var &vars; 
	   where State = &state and Age_Range = &age_range; 
	   title "Detail Listing of Account in &state and current date &sysdate"; 
	   run; 
	   
	   title "Total Balance in &state and in age &age_range current date &sysdate"; 
	   proc sql; 
	   select %list2sql(&vars), 
	          sum(Balance) as Total_Balance 
			  from &dsn where State = &state and Age_Range = &age_range; 
	   quit; 
	%end; 
%mend cust; 
%cust(dsn = datasetname, vars =Acct_ID Age Balance State, 
      age_range= "18-40", state = "NY");

Re: Macro Variables in Proc sql

Shmuel — Sun, 25 Nov 2018 06:27:54 GMT

alternatively use:
proc sql;
select %substr(&vars,2,%length(&vars)-2)

where vars = "Acct_ID, Age, Balance, State"

Re: Macro Variables in Proc sql

Tom — Sun, 25 Nov 2018 06:59:49 GMT

So if you are setting the macro variable VARS to a space delimited list of variable names.

%cust(dsn=datasetname,vars=Acct_ID Age Balance State,age_range="18-40",state="NY");

Then that will work well for the VAR statement of PROC PRINT.

var Acct_ID Age Balance State;

But not work well for the columns list in a PROC SQL SELECT statement. For SQL you want commas instead of spaces between the variable names. Like :

... select Acct_ID,Age,Balance,State ...

It is easy to convert using %SYSFUNC() to call the TRANWRD() (or TRANSLATE()) function.

select %sysfunc(tranwrd(&vars,%str( ),%str(,)))

Just make sure that there is one and only one space between the variable names. Which you can do with the COMPBL() function.

select %sysfunc(tranwrd(%sysfunc(compbl(&vars)),%str( ),%str(,)))