Problem
I am performing repetitive queries on tables with two kinds of variables, lets call them samevar1-m, diffvar1-n.
I have no idea now to create a macro where I can enter the disired number of variables because once I compile it, the number cannot me changed. How can I make it more flexible?
Is there any way to create a macro
%macro analysis (m, samevar1-m, n, diffvar1-n);
....
%mendo analysis (m, samevar1-m, n, diffvar1-n);
that uses the desired number of variables?
Hi,
You could make it 100% dynamic by first querying the dictionnary view containing the column names. This way, you can count how many variables are named like 'samevar%' and then make a loop on these variables.
I would do it as follow:
proc sql;
select count(distinct name)
into :m
from sashelp.vcolumn
where upcase(libname) = 'YOUR_LIBNAME'
and upcase(memname) = 'YOUR_TABLENAME'
and upcase(name) like 'SAMEVAR%';
quit;
Then you can use the macro-variable to loop on your variables "Samevar", with something as follow (or any other logic you want to apply):
proc sql;
select Samevar1-Samevar%left(&m.)
into :Samevar1-:Samevar%left(&m.)
from your_libname.your_tablename;
quit;
%macro analysis;
%do i= 1 %to %left(&m);
%put Samevar&i = &&Samevar&i.;
%end;
%mend analysis;
%analysis
I hope it helps.
Regards,
Florent
You don't give any example of what you're doing but another approach is to provide a "base" for the variable and then suffixes;
If numeric it could be something like this:
%macro process(base=samevar, startsuf=1, endsuf=10);
%do i = &startsuf %to &endsuf;
proc freq data=dsn;
tables &base.&i;
end;
%end;
%mend;
If you have two variables with the same suffixes you would add anothe "base". if the suffixes weren't the same add suffix variables.
I am not sure what the issue here is.
Being able to provide the input parameters is why you would want to create a macro!!
So if your analysis requires two lists of variables then give the user two macro parameters for them to specify the list.
%macro analysis(data=,list1=,list2=);
proc freq data=&data ;
tables (&list1)*(&list2) ;
run;
%mend analysis;
Thank you all for you replies.. However I meant something totally different. I have the following macro:
*---------------------------------------------------------------------------------------------------+
| Auffinden von allen &Var1 mit verschiedenen Einträgen in der &Var2 in der Tabelle &Source |
| !!! ALLE Inputvariablen müssen befüllt sein !!! |
| Die Variable &Resultvar wird in der Tabelle &Result zusätzlich ausgegeben |
*---------------------------------------------------------------------------------------------------;
%Macro doppel (Source, Var1, Var2, Resultvar, Result);
proc sql;
/* Selektion aller vorhandenen, verschiedenen Paare von Var1 und Var2 */
create table paare as
select distinct &Var1, &Var2
from &Source;
/* Selektion aller Var1 mit mehreren Var2 */
create table doppel as
select distinct &Var1, count(*) as Anzahl
from paare
group by &Var1
having Anzahl > 1;
/* Selektion "Var1", "Var2" und zusätzlicher "Resultvar"iable mit mehrfachen "Namen" */
create table &Result as
select distinct r.&Var1, r.&Var2, r.&resultvar
from &Source as r
inner join doppel
on doppel.&Var1=r.&Var1;
quit;
%Mend doppel;
It is working. But I want to be able to select up to n Var1-Var_n and m Var_n+1-Var_m+n in the macro call. So how do I do that?
It looks like you are asking for a way to have a macro parse a variable list. For example, instead of using Var1 as the value of the second parameter, use something like var45-var63. If that's the case, here's the easy way:
proc contents data=&source (keep=&var1) noprint out=_contents_ (keep=name);
run;
That will get you a data set with all the proper variable names in it. It doesn't necessarily preserve the order, and Tom has published a neat way to use PROC TRANSPOSE to maintain the order. But order doesn't seem to matter in your program, so let's stick to the simple way.
Then create a set of macro variables as needed:
proc sql noprint;
select distinct(name) into : list1 from _contents_;
select distinct(name) into : list2 separated by ', ' from _contents_;
select distinct('r.' || name) into : list3 separated by ', ' from _contents_;
quit;
Good luck.
Thx... I'd do that, but how can I perform a join on the variable list?
That's just a more complex SELECT statement in the final SQL. You know the syntax you're looking for better than I, but something along these lines would be close:
select distinct('doppel.' || strip(name) || '=r.' || strip(name)) into : list4 separated by ', ' from _contents_;
The more I think about it, DISTINCT might be overkill here. There variable names are already distinct ... just a side issue though.
Good luck.
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.
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.