BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ashc25901
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

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

 

Shmuel
Garnet | Level 18
alternatively use:
proc sql;
select %substr(&vars,2,%length(&vars)-2)

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

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1299 views
  • 0 likes
  • 3 in conversation