BookmarkSubscribeRSS Feed
sfmeier
Obsidian | Level 7

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?

7 REPLIES 7
Florent
Quartz | Level 8

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

ballardw
Super User

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.

Tom
Super User Tom
Super User

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;

sfmeier
Obsidian | Level 7

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?

Astounding
PROC Star

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.

sfmeier
Obsidian | Level 7

Thx... I'd do that, but how can I perform a join on the variable list?

Astounding
PROC Star

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.

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
  • 7 replies
  • 1344 views
  • 0 likes
  • 5 in conversation