How to write a macro to count the variables in the end?

[ybz12003]

Hello, experts:

I have the follow macro program to change the column names. However, I have to manually count each variables to write the number at the end to make the macro program run. Is there a way the detect the how many variables and input the number automatically? Please see my attachment for details, the Oldprefix tab is needed to be corrected.   And the Newprefix tab is the standard name. The excel sheets are only the examples.   My actual dataset contains 30 variables which are very similar situation need to be changed. Thanks.

Best,

Y

%macro rename(oldprefix, newprefix, num);

%let k=1;

%do %while(&k <= &num);

rename &oldprefix.&k = &newprefix.&k;

%let k = %eval(&k + 1);

%end;

%mend rename;

data a ;

set il;

%rename(Gm_go, Yut_go,5);

%rename(gm_hhrt, Comp_notes_1,3);

%rename(gm_certificate, Data_certific,7)

run;

[PeterClemmensen]

Small example

%let nvar=%sysfunc(attrn(%sysfunc(open(sashelp.cars)),nvar));
%put &nvar ;

[ybz12003]

The codes you gave seems only count all of the variables.  I am looking for some specific varaibles.

[PeterClemmensen]

What specific varaibles are these? I don't want to download Excel files 🙂

[Astounding]

As long as you have to do the counting, just get rid of all the macro language entirely.  You can code:

rename Gm_go1- Gm_go5 = Yut_go1 - Yut_go5;

If you are going to run something like this many times, with different variables each time, we can revisit how to write a macro.  But so far it's not worth the effort for a one-time application.  You could write 30 RENAME statements faster than you could write, debug, and apply a macro for that purpose.  And remember, you don't have to count them.  PROC CONTENTS will list the names for you, complete with options that print the variable names in order according to the value of the numeric suffix.

Note:  Sorry, I can't program this for you.  Coding, testing, and explaining would take an hour, and I'm just not willing to spend that amount of time on it.

[ybz12003]

It's not one-time use.  I will use it every month.  In additions, the data sets will come from different sources.  For example, one comes to 'Gm_go' with 5 and the other comes to 'Gm_go' with 7.  In that way, I need to count it by myself if I don't set up a macro.  I am trying to find if there is a shortcut for counting.

[BrunoMueller · Accepted solution]

Find below sample code that shows some techniques on counting the number of variables that have a certain name. The example makes use of dictionary tables.

data have;
  length
    somevar1 $ 1
    other $ 2
    abc $ 3
  ;
  array gm_go{4};
run;

proc sql;
  select
    libname
    , memname
    , name
  from
    dictionary.columns
  where
    libname = "WORK"
    and memname = "HAVE"
    and lowcase(name) like 'gm_go%'
  ;

  %put NOTE: &=sqlrc &=sqlobs;

  reset noprint;
  select
    count(*)
    into
    :nVars trimmed
  from
    dictionary.columns
  where
    libname = "WORK"
    and memname = "HAVE"
    and lowcase(name) like 'gm_go%'
  ;
  %put NOTE: *&nVars*;
quit;

[ybz12003]

Awesome!  I will look into this.  Thanks.

[ybz12003]

Acturally, I use proc contents to put all of the columns names in vertical way, then 'where' statement and then proc mean to count each group.  But I found the 'Name' only contains 'gm_certificate' in the end.  Is there way keep all of the group names, including 'Gm_go','gm_hhrt', and 'gm_certificate',  in the column 'Name'?  Thanks.

proc contents data=oldname out=have (keep=NAME);

run;

data count;

set have;

where name contains 'Gm_go'; N1=1;

where name contains 'gm_hhrt'; N2=1;

where name contains 'gm_certificate'; N3=1;

run;

proc means data=count sum;

var N1 N2 N3;

run;