topic Re: Rename columns based on position (37 columns) in SAS Programming

Rename columns based on position (37 columns)

BlayLay — Thu, 17 Dec 2020 17:42:19 GMT

I have a .xlsx that has 37 different columns. After import, I want to rename them based on column position. For example, reference below mockup table, I'd like to change "Start Time" to "StartDate" by referencing the column number (Column 1) if possible, as there are 37 columns all with varying names and lengths of names and was looking for an easier way to rename. Some other examples I've found have limitations after 26 columns. If there is a way to do this on import, referencing column position, I can do that as well. I'm just not sure of how to approach this.

Start Time	Machine Name	Total Hours Worked

Re: Rename columns based on position (37 columns)

ballardw — Thu, 17 Dec 2020 17:57:04 GMT

Maybe the best approach to this sort of thing is to save the Excel file to CSV format and use a data step to read it with the variable names and properties you want.

I'm not sure where "column number" comes into this.

Proc datasets is designed to do things to existing data in place.

Since you show non-standard for SAS columns it might help to run Proc Contents on your existing data set and share the results.

Re: Rename columns based on position (37 columns)

PaigeMiller — Thu, 17 Dec 2020 18:01:25 GMT

Why do you need to reference column #1?

Can't you just rename in SAS using a RENAME statement? Something like this:

proc datasets library=work nolist;
     modify mydataset;
     rename start_time=startdate;
run;
quit;

If you know what each variable name is supposed to be renamed to, you can achieve this renamification (if that's a word) via either macros or CALL EXECUTE. Can you show us a portion of the list of renames by column?

Re: Rename columns based on position (37 columns)

BlayLay — Thu, 17 Dec 2020 18:59:40 GMT

Because I'll be renaming 37 columns, all with varying lengths. Was looking for a way of making it easier by referencing column position and just doing something like this:

RENAME col1 = 'StartTime'n
col2 = 'MachineName'n
etc
col37 = 'ZipCode'n

Re: Rename columns based on position (37 columns)

PaigeMiller — Thu, 17 Dec 2020 19:05:08 GMT

Varying lengths shouldn't matter, as long as the variable name is 32 characters or less.

Why create a variable named COL1 if its just an intermediary that isn't needed? This will work:

proc datasets library=work nolist;
     modify mydataset;
     rename 'Start Time'n=startdate 'Machine Name'n=machinename
         'Total Hours Worked'n=totalhours;
run;
quit;

Re: Rename columns based on position (37 columns)

Tom — Thu, 17 Dec 2020 19:05:58 GMT

I don't understand. In your original question you showed a spreadsheet with column headers. A normal reading of the spreadsheet by SAS (proc import or XLSX libname engins) would have caused to used as the variable names. Yet now in this post you are showing variables named COL1, COL2, etc.

Which of these do you have?

Show more details about what you are doing.

Re: Rename columns based on position (37 columns)

BlayLay — Thu, 17 Dec 2020 19:09:53 GMT

I was just providing "COL1" as a means to represent the column number, the column names remain what was mentioned in the original post. Looking to see if there is a way to rename columns based on column number (i.e. position).

Re: Rename columns based on position (37 columns)

PaigeMiller — Thu, 17 Dec 2020 19:15:14 GMT

@BlayLay wrote:
I was just providing "COL1" as a means to represent the column number, the column names remain what was mentioned in the original post. Looking to see if there is a way to rename columns based on column number (i.e. position).

I still don't understand. It seems as if you are trying to go

Start Time -> COL1 -> StartDate

instead of

Start Time -> StartDate

Re: Rename columns based on position (37 columns)

Tom — Thu, 17 Dec 2020 19:20:54 GMT

To rename a variable you need to know its name, its position does not matter. So for your first example name you would want to rename using one of these two forms, depending on whether your variable names were made use VALIDVARNAME=ANY or not.

rename Start_Time=StartDate;
rename 'Start Time'n=StartDate;

Is it possibly you wanted to match the names by position to some other list of names that you have?

data new_names ;
  varnum+1;
  input new_name $32.;
cards;
StartDate
;
proc contents data=have noprint out=contents;
run;
proc sql noprint;
select catx('=',nliteral(a.name),nliteral(b.new_name))
  into :renames separated by ' '
  from contents a inner join new_names b
  on a.varnum = b.varnum
;
quit;
proc datasets nolist lib=work;
  modify have ;
    rename &renames;
  run;
quit;

Re: Rename columns based on position (37 columns)

BlayLay — Thu, 17 Dec 2020 19:35:59 GMT

I think you answered my question "position does not matter". I was hoping to be able to reference the position to rename the column, rather than typing out each column name in the rename step.

Re: Rename columns based on position (37 columns)

ballardw — Thu, 17 Dec 2020 20:39:13 GMT

@BlayLay wrote:
I think you answered my question "position does not matter". I was hoping to be able to reference the position to rename the column, rather than typing out each column name in the rename step.

Here is a snipped to help reduce the typing:

proc sql;
   create table temp as
   select catx(' ', strip(name),'=','" "') as string
   from dictionary.columns
   where libname='SASHELP' and memname='CLASS'
   ;
quit;

data _null_;
  set temp;
  file print;
  put string;
run;

This will place something like this in the result window:That you can copy and put the new names into Rename syntax.

Name = " "                                                      
Sex = " "                                                       
Age = " "                                                       
Height = " "                                                    
Weight = " "

Replace each set of " " with the new name.

If have managed to create names with spaces then the above code would need

   select cats(quote(strip(name)),'n','=','" "') as string

to create name literals like "start time"n which is needed when using names with spaces or unusual characters not normally allowed in SAS variable names.

The dictionary tables maintain the metadata of your data sets in a number of tables. The Columns has the names and characteristics of all the variables in all your data sets. When you use this code the Libname must be used or you can get odd results from data sets with the same name in different libraries, the Libname and Memname are stored in upper case so the code needs to use the upper case names.

Re: Rename columns based on position (37 columns)

Tom — Thu, 17 Dec 2020 22:27:16 GMT

SAS has a function for generating name literals. NLITERAL()

Re: Rename columns based on position (37 columns)

ballardw — Thu, 17 Dec 2020 22:59:37 GMT

@Tom wrote:

SAS has a function for generating name literals. NLITERAL()

Thanks.

I think I may have noticed that at some point but I do not use name literals so it never stuck.