Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

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

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;

 


Accepted Solutions
Solution
‎06-13-2017 09:45 AM
SAS Super FREQ
Posts: 699

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

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;

View solution in original post


All Replies
PROC Star
Posts: 634

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

[ Edited ]

Small example

 

%let nvar=%sysfunc(attrn(%sysfunc(open(sashelp.cars)),nvar));
%put &nvar ;
Super Contributor
Posts: 297

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

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

PROC Star
Posts: 634

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

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

Super User
Posts: 5,255

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

[ Edited ]

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.

Super Contributor
Posts: 297

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

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.

Solution
‎06-13-2017 09:45 AM
SAS Super FREQ
Posts: 699

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

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;
Super Contributor
Posts: 297

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

Awesome!  I will look into this.  Thanks.

Super Contributor
Posts: 297

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 213 views
  • 3 likes
  • 4 in conversation