topic Re: Remove part of column name or rename multiple columns in SAS Programming

Remove part of column name or rename multiple columns

mariko5797 — Wed, 17 Aug 2022 16:59:24 GMT

I have multiple datasets: XP1, XP2, XP3, XP4. Each dataset contains the same type of information. However all the variables start with the name of the form.

For example, variables in XP1 will be named XP1INM, XP1INF, XP1INP, XP1INO; and variables in XP2 will be named XP2INM, XP2INF, XP2INP, XP2INO; etc.

I would ideally like to stack these datasets so that I don't have to repeat a DATA Step for every single XP dataset.

Is there a way to rename all the columns to remove the XP1, XP2, etc. prefixes so that my variables will be INM, INF, INP, INO?

I'd like to be able to do something similar to below, but with a lot more variables.

data xp;
 set xp1 xp2 xp3 xp4;
 if INM = 'Y' then source = 'Mother';
 if INF = 'Y' then source = 'Family member';
 if INP = 'Y' then source = 'Physician';
 if INO = 'Y' then source = 'Other';
run;

Re: Remove part of column name or rename multiple columns

ballardw — Wed, 17 Aug 2022 17:19:42 GMT

How are you bringing the data into SAS? If you have a program to read a text based file then you could read multiple files of similar structure and have the same variable names and just have the data set names differ (prevent the problem in the first place).

Proc datasets will rename variables in a data set. If the sets are in Work library replace YOURLIB with Work, or the name of the library.

IF you need a version of the data with the old variable names then COPY the data sets and do this to the copy.

proc datasets library=YOURLIB nolist;
   modify XP1 ;
      rename 
      XP1INM = Inm
      XP1INF = Inf
      XP1INP = Inp
      XP1INO = Ino
      ;
   modify XP2 ;
      rename 
      XP2INM = Inm
      XP2INF = Inf
      XP2INP = Inp
      XP2INO = Ino
      ;
   modify XP3 ;
      rename 
      XP3INM = Inm
      XP3INF = Inf
      XP3INP = Inp
      XP3INO = Ino
      ;
   modify XP4 ;
      rename 
      XP4INM = Inm
      XP4INF = Inf
      XP4INP = Inp
      XP4INO = Ino
      ;
quit;

Note that reading the data with a standard data step may prevent other possible issues such as different length or types of variables causing the combining data step to fail or truncate data.

Re: Remove part of column name or rename multiple columns

smantha — Thu, 18 Aug 2022 16:08:04 GMT

%macro prfx_name(prefix=prfx);
      rename = (
       %do i = 1 to %countw('inm inf inp ino');
           %let st1 = %scan('inm inf inp ino',&i.,' ');
             &prefix.&st1. =  &st1.
       %end;
)
%mend prfx_name;
data xp;
 set xp1(prfx_name(prefix='xp1'))
 xp2(prfx_name(prefix='xp2'))
 xp3(prfx_name(prefix='xp3')) 
xp4(prfx_name(prefix='xp4'));


run;