DATA Step, Macro, Functions and more

repeat same procedure for each variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

repeat same procedure for each variable

Dear All,

 

I want to use apply a same procedure on each variable. For example,

 

1. rename old variable  sex age  to sex_new   sex_old;

2. proc rank  each variable and fit model using ranks;

so on so forth.

 

I tried array and macro but just can't get it work........

 

Thanks a lot in advance!

 

Best wishes,

 


Accepted Solutions
Solution
‎07-22-2016 07:26 PM
SAS Super FREQ
Posts: 8,868

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao

Hi:

  This documentation example shows using GROUPS= with PROC RANK:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p1xzpoijq32wbsn1gr6g5... (Example 3)

 

  And assuming that you want all the numeric variables to be ranked, you can modify this example to make a macro variable to hold the names of all the numeric variables and then a second macro variable to hold the names of the "rank_" varible to be created.

 

  Here's the code (the PROC SQL step is the modification from the documentation):

 

options nodate pageno=1 linesize=80 pagesize=60;
data swim;
   input Name $ 1-7 Gender $ 9 Back 11-14 Free 16-19;
   datalines;
Andrea  F 28.6 30.3
Carole  F 32.9 24.0
Clayton M 27.0 21.9
Curtis  M 29.0 22.6
Doug    M 27.3 22.4
Ellen   F 27.8 27.0
Jan     F 31.3 31.2
Jimmy   M 26.3 22.5
Karin   F 34.6 26.2
Mick    M 29.0 25.4
Richard M 29.7 30.2
Sam     M 27.2 24.1
Susan   F 35.1 36.1
;
run;
    
proc sql;
  select name, catx('_','rank',name) into :vname separated by ' ',
                                          :rname separated by ' '
  from dictionary.columns
  where (libname='WORK' and memname = 'SWIM') and
        type = 'num';
 
quit;
  
%put vname is &vname;
%put rname is &rname;
    
proc sort data=swim out=pairs;
   by gender;
run;
    
proc rank data=pairs out=rankpair groups=3;
by gender;
var &vname;
ranks &rname;
run;
  
proc print data=rankpair n;
   by gender;
   title 'Pairings of Swimmers for Backstroke and Freestyle';
run;

And here are the results:

rank_rename.png

 

cynthia

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao

Please show the code of what you tried.

 

You are not going to be able to rename using array, that is either a rename statement or use proc datasets with a modify section for that dataset.

 

What type of rank? On which variables? What kind of model? What dependent variable(s)? What independent variables?

Frequent Contributor
Posts: 76

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao

Sorry i should have make my queations more clearly. Say I have the following data set:

 

grade     age

10            11

5              6

15            20

8              9

I want to use proc rank to rank the two variables in five groups:

proc rank groups=5 out=rank data=data;

var _ALL_;

ranks grade_rank age_rank;

run;

But since i have hundreds of variables, i cannot name every new names in 'ranks'. And i want the new coloumn names contains the original variable name then end or begins with 'rank'.

 

I'm thinking about using array or macro, but it doesn't work. I don't have that codes anymore, i deleted them already.....

 

The reason why i mentioned rename is that, now i'm using code:

proc rank groups=5 out=rank data=data;

var _ALL_;

ranks rank1-rank100;

run;

And i tried to rename rank1 - rank 100 to the name i desired. I cannot get this done neither........

 

Thanks again!!

 

Best,

 

 

Solution
‎07-22-2016 07:26 PM
SAS Super FREQ
Posts: 8,868

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao

Hi:

  This documentation example shows using GROUPS= with PROC RANK:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p1xzpoijq32wbsn1gr6g5... (Example 3)

 

  And assuming that you want all the numeric variables to be ranked, you can modify this example to make a macro variable to hold the names of all the numeric variables and then a second macro variable to hold the names of the "rank_" varible to be created.

 

  Here's the code (the PROC SQL step is the modification from the documentation):

 

options nodate pageno=1 linesize=80 pagesize=60;
data swim;
   input Name $ 1-7 Gender $ 9 Back 11-14 Free 16-19;
   datalines;
Andrea  F 28.6 30.3
Carole  F 32.9 24.0
Clayton M 27.0 21.9
Curtis  M 29.0 22.6
Doug    M 27.3 22.4
Ellen   F 27.8 27.0
Jan     F 31.3 31.2
Jimmy   M 26.3 22.5
Karin   F 34.6 26.2
Mick    M 29.0 25.4
Richard M 29.7 30.2
Sam     M 27.2 24.1
Susan   F 35.1 36.1
;
run;
    
proc sql;
  select name, catx('_','rank',name) into :vname separated by ' ',
                                          :rname separated by ' '
  from dictionary.columns
  where (libname='WORK' and memname = 'SWIM') and
        type = 'num';
 
quit;
  
%put vname is &vname;
%put rname is &rname;
    
proc sort data=swim out=pairs;
   by gender;
run;
    
proc rank data=pairs out=rankpair groups=3;
by gender;
var &vname;
ranks &rname;
run;
  
proc print data=rankpair n;
   by gender;
   title 'Pairings of Swimmers for Backstroke and Freestyle';
run;

And here are the results:

rank_rename.png

 

cynthia

 

Frequent Contributor
Posts: 76

Re: repeat same procedure for each variable

Posted in reply to Cynthia_sas
Hi Cynthia,
Thank you! I changed 'swim' in this code to the data name I have, but it doesn't work. And what is the meaning of 'dictionary.columns' ?
Thanks again!!
Best,
SAS Super FREQ
Posts: 8,868

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao
Hi:
WORK.SWIM is the name of the dataset I created in the very first step in the example (the code with the DATALINES.

Next, DICTIONARY.COLUMNS is a special table that is built for a SAS session that keeps track of the data that you are using. So in my PROC SQL step, when I have this:
where (libname='WORK' and memname = 'SWIM') and
type = 'num';

The library is WORK and the dataset name is SWIM. In your example, you did not show your library or dataset name. You can't just change SWIM to your data name. You will have to change the LIBNAME too. And DICTIONARY.COLUMNS stores the values in UPPER case, so you need to alter the code appropriately.

cynthia
Frequent Contributor
Posts: 76

Re: repeat same procedure for each variable

Posted in reply to Cynthia_sas
I see, thank you!
I just have one more question. So right now &vname &rname stores all variable names in data swim. Say in future I only need the rank of variable free. is there a way to use part of the &rname?
I tried:
proc rank data=modeldata out=rank groups=5;
var &vname[2];
ranks &rname[2];

it doesn't work.

Thanks again!

Best,
run;
Frequent Contributor
Posts: 76

Re: repeat same procedure for each variable

Posted in reply to Cynthia_sas
Hi Cynthia,
I just realized the reason it doesn't work is because I didn't capitalize my data name. So 'SWIM' works but 'swim' doesn't. Why is this? I thought SAS doesn't differentiate case.

SAS Super FREQ
Posts: 8,868

Re: repeat same procedure for each variable

Posted in reply to Xiaoningdemao
As I explained, the values in DICTIONARY.COLUMNS are physically stored in UPPERCASE. So, as for ANY comparison, you must do this:
where libname='WORK' or where LIBname='WORK'

notice that the case of LIBNAME is irrelevant, but the case of the value for LIBNAME is case-sensitive.

And, regarding this -- the short answer is no...you are wanting to treat the list like it is an array and this is an incorrect understanding of what is stored in the macro variable. So what you propose will not work:


proc rank data=modeldata out=rank groups=5;
var &vname[2];
ranks &rname[2];

cynthia
Frequent Contributor
Posts: 76

Re: repeat same procedure for each variable

Posted in reply to Cynthia_sas
I see. Thank you!!
Have a nice weekend Smiley Happy
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 520 views
  • 3 likes
  • 3 in conversation