SAS: Changing multiple variable names

Question

This is my current issue:

I have 53 variable headers in a SAS data set that need to be changed, for example:

Current_Week_0 TS   |    Current_Week_1 TS    |    Current_Week_2 TS  -- etc.

I need it to change such that Current_Week_# TS = Current_Week_# -- dropping the TS

Is there a way to automate this such as looping it like:

i = 0,53
Current_week_i TS = Current_Week_i  ?

I just don't understand the proper syntax.

Edit: Thank you for editing my formats Sergiu, appreciate it! :)

Edit:

I used the following code, but I get the following error:
Missing numeric suffix on a numbered variable list (TS-Current_Week_53)

     DATA True_Start_8;
        SET  True_Start_7;
          ARRAY oldnames (53) Current_Week_1 TS-Current_Week_53 TS;
          ARRAY newnames (53) Current_Week_1-Current_Week_53;
          DO i = 1 TO 53;
               newnames(i) = oldnames(i) ;
          END;
     RUN;

@Joe EDIT

Here's what the data looks like before and after the "denorm" / transpose

BEFORE

Product     ID      CurrentWeek         Market  TS
X           75av2kz     Current_Week_0  Z       1
Y           7sav2kz     Current_Week_0  Z       1
X           752v2kz     Current_Week_1  Z       1
Y           255v2kz     Current_Week_1  Z       1


Product     ID      Market    Current_Week_0_TS    Current_Week_1_TS
X           75av2kz     Z         1                    0
Y           7sav2kz     Z         1                    1
X           752v2kz     Z         1                    1
Y           255v2kz     Z         1                    0

Is this what you want: http://stackoverflow.com/questions/5056397/in-sas-use-loop-to-rename-indexed-columns ? — Jerzy Pawlikowski, Nov 14 '13 at 16:14
Hi Barjey - I appreciate the response, and I actually attempted to use a code that was in that previously answered question. I think my problem is the "TS" at the end that makes it hard for SAS to follow the looped 'i' statement? Above I added the code I attempted to use. — mmichaelx, Nov 14 '13 at 16:25
I'm still a noobie at SAS so I hope I can answer this properly - What I did to get these headers is that I: 1. Created a column that grouped date variables by a given week, hence the Current_Week_# 2. I denormed my data by Current_Week_# and that's what brought up the TS to the top ---- Does that make sense? — mmichaelx, Nov 14 '13 at 16:29
Your variable name that you list here has a space. Spaces aren't legal in SAS variable names in most contexts. Hence my question. Variable Labels are nice-looking things you can read but can't use in programming. Variable names are the actual names, with no spaces. — Joe, Nov 14 '13 at 16:54
Also, the answer probably is in your transpose. Can you post your transpose code (what you call 'denormed')? Maybe a bit of example (dummy) data that is pre-transpose? — Joe, Nov 14 '13 at 16:56
That's good to know about labels vs names - The transpose I completed using a company macro called %mdenorm - I don't know that actual underpinnings to it I can post some dummy data above that shows what it does though — mmichaelx, Nov 14 '13 at 17:05

Joe · Answer 1 · 2013-11-14T16:53:11.000

0

This isn't too hard. I assume these are variable labels.

    proc sql;
    select cats('%relabel_nots(',name,')') into :relabellist separated by ' '
    from dictionary.columns
    where libname='WORK' and memname='True_Start_7'
    and name like '%TS';  *you may need to upper case the dataset name (memname) depending on your OS;
    quit;
%macro relabel_nots(name);
label &name.= substr(vlabel(&name.),1,length(vlabel(&name.))-3);
%mend relabel_nots;

data want;
set True_Start_7;
&relabellist.;
run;

Basically the PROC SQL grabs the different names that qualify for the relabelling, and generates a large macro variable with all of the rename macro calls. The relabel_nots macro generates the new labels. You may need to change the logic behind the WHERE in the PROC SQL if the variable names don't also contain the TS.

edited Nov 14 '13 at 16:53

answered Nov 14 '13 at 16:28

Joe

62,789
6
49
67

Hi @Joe - I really appreciate your help, and I'm excited to dive into this code to thoroughly understand what it is doing -- but when I run it in my current code I get the following error `601 data want;` `602 set True_Start_7;` `603 &relabellist.;` `-` `180` `WARNING: Apparent symbolic reference RELABELLIST not resolved.` `ERROR 180-322: Statement is not valid or it is used out of proper order.` `604 run;` – mmichaelx Nov 14 '13 at 16:36
i think the proc sql is just missing the `quit;` after the line `and name like '%TS';` – scott Nov 14 '13 at 16:41
@scott I added the `quit;` and it still created the same error -- as he was mentioning about label versus name - that part is slightly confusing to me a little bit~ – mmichaelx Nov 14 '13 at 16:46
@joe @scott When I open up "true_start_7" and double click the headers, it gives me that it is Current_Week_#_TS with an undesscore between, while when I use FSVIEW there is no underscore. Does matter in terms of `:relabellist separated by ' '` – mmichaelx Nov 14 '13 at 16:54
That sounds like the name is '_TS' and the label is ' TS'. That's a reasonable thing to guess given SAS often replaces illegal characters with _. Are you trying to change the names to not have the _TS or trying to change the label? Or both? – Joe Nov 14 '13 at 16:55
@Joe - Well in the end, my goal is to output this dataset to Excel (the very next step after this current name change code) and that these headers for each column _do not_ have the TS. It wouldn't be to big of a deal to do manually, but it is a weekly process. – mmichaelx Nov 14 '13 at 16:58
@Joe - wouldn't it be better to do the rename in PROC DATASETS. That way, just the descriptor portion of the dataset is changed without having to read in any of the rows. – Longfish Nov 14 '13 at 17:43

score 0 · Answer 2 · answered Nov 14 '13 at 18:59

Another option is to do this in the transpose. Your example data either doesn't match the example desired output, or there is something in logic not explained, but this does the simple transpose; if there is a logical reason that the current_week_0/1 are different in yours than in the below, explain why.

data have;
format currentWeek $20.;
input Product  $ ID  $ CurrentWeek $ Market $ TS;
datalines;
X           75av2kz     Current_Week_0  Z       1
Y           7sav2kz     Current_Week_0  Z       1
X           752v2kz     Current_Week_1  Z       1
Y           255v2kz     Current_Week_1  Z       1
;;;;
run;

proc sort data=have;
by market id product;
run;
proc transpose data=have out=want;
by market id product ;
id currentWeek;
var TS;
run;

SAS: Changing multiple variable names

2 Answers2