- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have a dataset with about 100 variables all starting with the same prefix (and ending in different numbers, like prefix1, prefix3, prefix7...prefix170, etc.). The numbers are not consecutive. All I want to do is rename the variables prefix1_2008, prefix3_2008, etc. There are too many variables to type them all by hand, so I'm hoping there's some kind of shortcut.
Any help is much appreciated.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Firstly my question would be why? The reason is that SAS has native functionality to deal with lists of variables with a numeric suffix, if you apply your logic to them they lose all that functionality. For instance an array statement of:
array some_name{200} $200. prefix1-prefix200;
becomes
array some_name{200} $200. prefix1_2008 prefix2_2008... prefix200_2008;
So far more typing. Over and of functions and such like also work lists of variables so you would lose that as well.
If there is a specific reason why you need 2008 as part of the variable name then I would suggest you need to think about your data differently, normalize it:
... prefix year
... 1 2008
... 2 2008
Then use summary functions.
As for technically, then yes, there are many postings on here with methods to do this.
data _null_;
set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;
if _n_=1 then call execute('data want; set have (rename=('||strip(name)||'='||strip(name)||'_2008');
else call execute(' '||strip(name)||'='||strip(name)||'_2008');
if last then call execute(';run;');
run;
Though do think about what you are going to do further with the data as my question above.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You can try proc transpose to stack all these variables, which you get a column Old_vari with previous variable name prefix1, prefix3, prefix7....as value.
Then add a new column as New=Old-vari||'_2008', and use this newly created variable after ID statement in another proc transpose, in which you un-stack the table and get values in New column as variable names.
syntax of proc transpose for stack table
proc transpose data=you_input_data out=stack_table (rename=(col1=value))
Name Old_vari;
By xxxxx /*columns you want to keep unchanged*/
run;
Data stack_table1;
set stack_table;
New=Old_vari||'_2008';
drop Old_vari;
run;
Proc transpose data=stack_table1 out=you_output_data;
ID New;
Var Value;
By xxxx;
Run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Adding 2008 to the end will break your nice variable list. Look how easy it could be if you just move 2008 to the "left".
length prefix1 prefix3 prefix7 prefix170 8;
retain _all_ 0;
run;
proc print;
run;
options dkricond=nowarning;
data test2;
set test(rename=(prefix1-prefix170=prefix2008_1-prefix2008_170));
run;
options dkricond=error;
proc print;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Explore using PROC DATASETS / MODIFY with RENAME -- SAS code can be generated then %INCLUDE, based on the output of a PROC CONTENTS with OUT= or otherwise manually coded if/where the SAS variable names are known and expected to be present.
Scott Barry
SBBWorks, Inc.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc datasets + call execute() , the benefit is fast speed, no need to overwrite dataset again.
data test; length prefix1 prefix3 prefix7 prefix170 8; retain _all_ 0; run; data _null_; set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='TEST' and name like 'prefix%')) end=last; if _n_ eq 1 then call execute('proc datasets library=work nolist; modify test;rename '); call execute(cats(name,'=',name,'_2008')); if last then call execute(';quit;'); run;
Xia Keshan
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Below is a way that I have been able to put an "E_" in front of all of my variables. Many thanks especially to data _null_ and Joe Matisse for helping me along the way.
It also has a little code to prevent particular variables from receiving the E_.
proc sql noprint;
select trim(name)||'= E_'||trim(name)
into :varlist separated by ' '
from DICTIONARY.COLUMNS
WHERE LIBNAME EQ "WORK" AND MEMNAME EQ "FLATENDORSEMENT"
and upcase(name) ne 'POL_PHR_CODE' and upcase(name) ne 'POL_CODE' and upcase(name) ne 'POL_CMP_CODE';
quit;
%put &varlist;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do consider that SAS has a macro variable maximum length limitation which could result in variable-name truncation. Best to avoid the potential situation, program to write SAS code (with a DATA step) to generate the statements needed to perform the RENAME, reading either DICTIONARY or SASHELP view for determining an input variable list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
length prefix1 prefix3 prefix7 prefix170 8;
retain _all_ 0;
run;
proc contents data=have out=interim
(where=(index(upcase(name),'PREFIX') > 0));
run;
data interim ;
set interim;
name_2008 = cats(name,'_2008');
rename_stmt = cats(name,' = ',name_2008);
run;
proc sql;
select rename_stmt into :rn_st separated by ' ' from interim;
quit;
data have (rename = ( &rn_st ) );
;
set have;
run;