topic Re: repeat same procedure for each variable in SAS Programming

repeat same procedure for each variable

Xiaoningdemao — Thu, 21 Jul 2016 20:38:24 GMT

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,

Re: repeat same procedure for each variable

ballardw — Thu, 21 Jul 2016 22:05:59 GMT

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?

Re: repeat same procedure for each variable

Xiaoningdemao — Fri, 22 Jul 2016 17:13:14 GMT

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,

Re: repeat same procedure for each variable

Cynthia_sas — Fri, 22 Jul 2016 17:46:11 GMT

Hi:

This documentation example shows using GROUPS= with PROC RANK:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p1xzpoijq32wbsn1gr6g5cx3emsx.htm (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:

cynthia

Re: repeat same procedure for each variable

Xiaoningdemao — Fri, 22 Jul 2016 22:21:23 GMT

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,

Re: repeat same procedure for each variable

Xiaoningdemao — Fri, 22 Jul 2016 22:27:47 GMT

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.

Re: repeat same procedure for each variable

Cynthia_sas — Fri, 22 Jul 2016 22:28:50 GMT

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

Re: repeat same procedure for each variable

Xiaoningdemao — Fri, 22 Jul 2016 22:44:10 GMT

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;

Re: repeat same procedure for each variable

Cynthia_sas — Fri, 22 Jul 2016 22:58:20 GMT

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

Re: repeat same procedure for each variable

Xiaoningdemao — Fri, 22 Jul 2016 23:27:28 GMT

I see. Thank you!!
Have a nice weekend 🙂