topic Re: Renaming many variables at once from a data set list in SAS Programming

Renaming many variables at once from a data set list

jcis7 — Sat, 09 Sep 2017 00:30:46 GMT

%let prioryr=PY;

data want;
	set have;
  array rename

    TOT_ENROL TOT_ALLRE TOT_DTP TOT_POLIO TOT_MMR TOT_HEPB TOT_VARI

    PB_ENROL PB_ALLRE PB_DTP PB_POLIO PB_MMR PB_HEPB PB_VARI

    PR_ENROL PR_ALLRE PR_DTP PR_POLIO PR_MMR PR_HEPB PR_VARI;

	do i=1 to dim(rename);
	rename(i)=rename(i)_&prioryr;
	end;
run;

Hello,

I have the SAS code above with current variable names (more then those listed but I shortened the list). I need to rename the variables by adding the prior year (&prioryr) to the end of each variable name, ie TOT_ENROL_PY TOT_ALLRE_PY ...etc. PB_ENROL_PY, PB_ALLRE_PY..ETC, PR_ENROL_PY, PR_ALLRE_PY...etc

I tried using an array but that didn't work.

I realized an array changes the value of the variable itself vs its actual name. Is there an alternative to using the rename statement for each variable in the set statement and dropping the old variables in the data statement? Any help is much appreciated! Thanks so much!

Re: Renaming many variables at once from a data set list

Patrick — Sat, 09 Sep 2017 01:13:10 GMT

@jcis7

Something like below should do.

data sample;
  array have {*} 8
    TOT_ENROL TOT_ALLRE TOT_DTP TOT_POLIO TOT_MMR TOT_HEPB TOT_VARI
    PB_ENROL PB_ALLRE PB_DTP PB_POLIO PB_MMR PB_HEPB PB_VARI
    PR_ENROL PR_ALLRE PR_DTP PR_POLIO PR_MMR PR_HEPB PR_VARI;
run;

%let prioryr=PY;
proc datasets lib=work nolist;
  modify sample;
  rename 
    TOT_ENROL =TOT_ENROL_&prioryr
    TOT_ALLRE =TOT_ALLRE_&prioryr
    TOT_DTP =TOT_DTP_&prioryr
    TOT_POLIO =TOT_POLIO_&prioryr
    TOT_MMR =TOT_MMR_&prioryr
    TOT_HEPB =TOT_HEPB_&prioryr
    TOT_VARI =TOT_VARI_&prioryr
    PB_ENROL =PB_ENROL_&prioryr
    PB_ALLRE =PB_ALLRE_&prioryr
    PB_DTP =PB_DTP_&prioryr
    PB_POLIO =PB_POLIO_&prioryr
    PB_MMR =PB_MMR_&prioryr
    PB_HEPB =PB_HEPB_&prioryr
    PB_VARI =PB_VARI_&prioryr
    PR_ENROL =PR_ENROL_&prioryr
    PR_ALLRE =PR_ALLRE_&prioryr
    PR_DTP =PR_DTP_&prioryr
    PR_POLIO =PR_POLIO_&prioryr
    PR_MMR =PR_MMR_&prioryr
    PR_HEPB =PR_HEPB_&prioryr
    PR_VARI =PR_VARI_&prioryr
  ;
  run;
  contents data=sample;
  run;
quit;

Re: Renaming many variables at once from a data set list

jcis7 — Sat, 09 Sep 2017 01:39:59 GMT

It worked to rename the variables. However, my original dataset has number values for each variable. How do I rename the variables and retain the values of each observation? Sorry I wasn't clearn. Thank you!

Re: Renaming many variables at once from a data set list

Patrick — Sat, 09 Sep 2017 01:43:38 GMT

@jcis7

Sorry but I don't understand your question/the remaining problem. Can you please explain a bit more in detail ideally illustrated with some sample data?

Re: Renaming many variables at once from a data set list

Reeza — Sat, 09 Sep 2017 03:07:29 GMT

You'll want a method that's more dynamic, consider using the SASHELP tables to build your rename statement.

Here's an example that renames variables wiht the suffix of _DATE.

https://gist.github.com/statgeek/82d9f2854edc01560e0f

Re: Renaming many variables at once from a data set list

Tom — Sat, 09 Sep 2017 04:06:55 GMT

Your syntax is wrong. You cannot use array references in a RENAME statement, just the actual names.

If sounds like you want some method to make it easier to add a suffix to a series of names?

Personally if you are just doing it once then any good editor should let you generate the statement with a couple of copy and pastes.

You could also write a quick program to generate a macro variable with the list of oldname=newname pairs.

%let varlist= 
TOT_ENROL TOT_ALLRE TOT_DTP TOT_POLIO TOT_MMR TOT_HEPB TOT_VARI 
PB_ENROL PB_ALLRE PB_DTP PB_POLIO PB_MMR PB_HEPB PB_VARI 
PR_ENROL PR_ALLRE PR_DTP PR_POLIO PR_MMR PR_HEPB PR_VARI
;
data _null_;
  length old $32 rename $4000 ;
  do i=1 to countw("&varlist");
    old=scan("&varlist",i);
    rename=catx(' ',catx('=',old,catx('_',old,'PY')));
  end;
  call symputx('rename',rename);
run;

And then use the macro variable in a RENAME statement. Either in a simple data step or a dataset option or even a PROC DATASETS step.

data want;
  set have;
  rename &rename ;
run;

Your list looks even easier as it apears to really be three lists with different prefixes.

data _null_;
  length prefix old $32 rename $4000 ;
  do prefix='TOT','PB','PR';
    do old = 'ENROL','ALLRE','DTP','POLIO','MMR','HEPB','VARI';
       old=catx('_',prefix,old);
       rename=catx(' ',catx('=',old,catx('_',old,'PY')));
    end;
  end;
  call symputx('rename',rename);
run;

Re: Renaming many variables at once from a data set list

Reeza — Sat, 09 Sep 2017 04:09:21 GMT

Or, assuming this is because you have multiple years of data, append the data NOT merge, and then use OROC TRANSPOSE to flip the data.

Re: Renaming many variables at once from a data set list

Ksharp — Sun, 10 Sep 2017 12:24:38 GMT


Here is an example.



data class;
 set sashelp.class;
run;





%let prioryr=PY;
data _null_;
 set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='CLASS')) end=last;
 if _n_=1 then call execute('proc datasets library=work nolist nodetails; modify class;');
 call execute(cat('rename ',name,'=',cats(name,"_&prioryr"),';'));
 if last then call execute(';quit;');
run;