CODEWITHSUNDEEP
sas
user2568648
user2568648

Reputation: 3181

sas add prefix to column names

I need to add a prefix to certain column names in a table. The names at the moment range from _15 to _49 and I would simply like to add the prefix N to give N_15,...,N_49 etc.

I tried the following:

proc sql noprint;
select cats(name,'=','N',name)
into :prefixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'Freq_nais_2006_2010';
quit;

However this does nothing as I just get the message no rows were selected in the log output. What must I change?

Upvotes: 2

Views: 8486

Answers (4)

vasja
vasja

Reputation: 4792

You're very close:

proc sql noprint;
select cats(name,'=','N',name)
into :prefixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'FREQ_NAIS_2006_2010' 
         /* and substr(NAME,1,1) = '_' - can add condition on column name pattern */;
quit;

proc datasets lib=WORK nolist nodetails;
modify FREQ_NAIS_2006_2010;
rename 
&prefixlist
;
quit;

Changed separator to space for use in PROC DATASETS; MODIFY ... RENAME ... statement. Side note: the datastep variant answers rewrite the dataset completly, which is ineffective and dangerous for real world usage (big tables), also much less clear on what you're doing.

Upvotes: 2

Reeza
Reeza

Reputation: 21264

You can just list them in the rename statement in your code, no need for macros or anything else. Though the best idea is avoid it in the first place if you can. See the rename statement below.

data test;
    array test(20) _1-_20;
    do i=1 to 20;
        test(i)=rand('normal', 20);
    end;
run;

data test2;
    set test;
    rename _1-_20 = n_1-n_20;
run;

Upvotes: 0

Joe
Joe

Reputation: 63424

Your particular issue is that the WHERE clause is not being fulfilled by any rows, likely because of this: and memname = 'Freq_nais_2006_2010'. Member names are typically capitalized internally in SAS, even if they're not capitalized in your code.

Otherwise your code looks fine, and you should be able to use that &prefixlist. in a PROC DATASETS or data step rename statement. I generally suggest the PROC SQL method as it's easier to customize to specify the variables you want to rename, but of course if you're renaming all of the variables in the dataset the macro works as well.

Upvotes: 2

user2568648
user2568648

Reputation: 3181

I managed to find the following code from the sas website (http://support.sas.com/kb/37/433.html):

%macro vars(dsn,chr,out);                                                                                                               
   %let dsid=%sysfunc(open(&dsn));                                                                                                        
   %let n=%sysfunc(attrn(&dsid,nvars));                                                                                                 
   data &out;                                                                                                                            
     set &dsn(rename=(                                                                                                                    
     %do i = 2 %to &n;                                                                                                                 
     %let var=%sysfunc(varname(&dsid,&i));                                                                                            
     &var=&chr&var                                                                                                              
      %end;));                                                                                                                            
      %let rc=%sysfunc(close(&dsid));                                                                                                        
  run;                                                                                                                                  
%mend vars;

%vars(Freq_nais_2006_2010,N,Freq_nais_2006_2010);

Upvotes: 0

Related Questions