How to create a macro using SYMPUTX if the name of the macro you want to create contains a macro?

[EC27556]

Hi,

I am trying to create macro variables to identify how many variables start with "GROUP" in a number of datasets.

For each dataset I want a macro variable called Tree_Group&i. where &i. relates to the dataset. Does anyone know how to achieve this? I know you cant use a macro variable in symputx when creating a new macro so unsure how to proceed as the below wont work!

%macro Test(i);
proc contents
     data = Test_&i.
          out = data_info&i.(where=(upcase(substr(name,1,5)="GROUP"))
               keep = name);
run;

data _NULL_;
set data_info&i. nobs=GROUP_NUMBER;
call symputx(Tree_Group&i,GROUP_NUMBER);
run;

%mend;
%Test(1);
%Test(2);

[PaigeMiller · Accepted solution]

Try this:

call symputx("Tree_Group&i",GROUP_NUMBER);

If the data sets have the same name except for consecutive numbering at the end of the data set name, there's really no need to do this via macros. PROC FREQ working on SASHELP.VCOLUMN ought to produce this without macros and without loops.

[Tom]

 I know you cant use a macro variable in symputx when creating a new macro so unsure how to proceed as the below wont work!

That is just a flat out false statement.

This line in your code:

call symputx(Tree_Group&i,GROUP_NUMBER);

Is looking for a variable named TREE_GROUP1 or TREE_GROUP2 that contains the name of the macro variable you want to create.

If instead you want it to create a macro variable named TREE_GROUP1 or TREE_GROUP2 then you need to pass that STRING to the function call.

call symputx("Tree_Group&i",GROUP_NUMBER);

[Quentin]

Hi,

Note there are some other issues in your macro as well.  There is a parentheses problem, and you will also need to add the "G" parameter to CALL SYMPUTX assuming your goal is to make a global macro variable:

%macro Test(i);
proc contents
     data = Test_&i.
          out = data_info&i.(where=(upcase(substr(name,1,5))="GROUP")  /*fixed parentheses*/
               keep = name);
run;

data _NULL_;
set data_info&i. nobs=GROUP_NUMBER;
call symputx("Tree_Group&i",GROUP_NUMBER,"G");    /*added quotes to first argument, and third argument G to make global macro var*/
stop ;  /*you only need to read one record*/ 
run;

%mend;

data test_1  ;
  array foo{*} Group1 Grouper NotGroup ;
run ;

%Test(1)

%put &Tree_group1 ;

Note if you're going to build a macro, and are interested in macro programming, you might think about options for making this a more general macro, e.g. below macro uses your core approach, but allows user to input the dataset name, variable name prefix, and name of the global macro variable to be created:

%macro CountVars
  (data=
  ,prefix=
  ,macvar=
   );

proc contents 
  data = &data (keep=&prefix: )
  out = __MyVars(keep = name) 
  noprint
;
run;

data _NULL_;
  set __MyVars nobs=GROUP_NUMBER;
  call symputx("&macvar",GROUP_NUMBER,"G");   
  stop ;  
run;

proc delete data=__MyVars ;
run ;

%mend;

%CountVars
  (data=Test_1
  ,prefix=Group
  ,macvar=GroupCount1
   )

%put &groupcount1 ;

Another macro solution would be to write a function-style macro %VarList which allows a user to input a dataset and will return a list of the variables found.  Such macros exist in user group papers in lexjansen.com, they are intermediate / advanced macro coding.  If you have the macro, and then you could find the count with just:

%put There are %sysfunc(countw(%varlist(mydata(keep=Group: )))) variables in work.mydata that have a variable name starting with GROUP ;

There is complexity to writing a VARLIST macro that allows you to specify keep/drop options in the usual SAS way, but once you have such a function-style macro, it becomes handy in many situations.

[xxformat_com]

Alternative solution: use dictionaries - Here is an example which only create the macro variable is there is at least one group variable. You can extend it with all the cases with a merge...

data one;
    group_A=1;
    group_B=1;
    tmp=1;
run;

data two;
    group=1;
run;

data three;
    tmp=1;
run;


proc sql;
    create table ref as
    select distinct memname, count(*) as cnt_group
    from dictionary.columns
    where upcase(libname)='WORK' and
          upcase(name) like 'GROUP%'
    group by memname;
quit;


data _null_;
    set ref;
    call symputx (cats(memname,_n_),cnt_group);
run;

[Tom]

Why?

What is the purpose of stuffing so much information into macro variables?

What are you going to do with the macro variables?

If you want a list of the variables for a given dataset why not put the list into one macro variable?