BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zachary_oliver
Calcite | Level 5

Hi, I am attempting to rename 417 columns labeled COL1-COL417 using names from a separate data set. (see the data set below)

I have a separate data set with one column that has all 417 names that I would like to use to replace COL1-COL417. What is an efficient way to approach this?

Thank you!

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

You might take advantage of the SQL UNION operator which will append a table by column position rather than name.

Base SAS(R) 9.2 Procedures Guide

/* get column names */

Proc SQL NoPrint ;

     Select names

     Into :colnames

     Separated by ' '

     From namelist

     ;

Quit ;

/* create empty table */

Data temp ;

     Length Industry_IO_Code $ 8

               &colnames 8

               ;

Run ;

/* append data */

Proc SQL ;

   Create table want as

     Select * from temp (obs = 0)

          Union

     Select * from have

     ;

Quit ;

(Untested code)

Richard

Message was edited by: Richard Carson Apologies: Forgot the From clause in the first SQL

View solution in original post

5 REPLIES 5
Reeza
Super User

Depends how automated you want it.

One quick way, is in the excel sheet create a concatenate formula in the third column that creates the field as follows:

=concatenate(A1, " = ", B1)

Col1=New_Name1

Then copy and paste that into a code window as follows:

Data rename;

set have;

rename

*Paste the excel formula output here;

;

run;

zachary_oliver
Calcite | Level 5

Thanks for the quick response.

I would like to automate this process because it is something that I will be running for data sets with varying numbers of columns. Therefore, taking myself out of the program would be ideal.

art297
Opal | Level 21

If I understand what you are trying to do, you might be able to use something like the following:

/*create some test data*/

data have;

  input iocode $ col1 col2 col3;

  cards;

1111A0 1 2 3

1111B0 4 5 6

111200 7 8 9

;

/*add record number to the data*/

data need;

  set have;

  recnum=_n_;

run;

proc sql noprint;

  select "col"||strip(put(recnum,3.))||"=_"||strip(iocode)

    into :renames separated by " "

      from need

  ;

quit;

data want;

  set have (rename=(&renames.));

run;

RichardinOz
Quartz | Level 8

You might take advantage of the SQL UNION operator which will append a table by column position rather than name.

Base SAS(R) 9.2 Procedures Guide

/* get column names */

Proc SQL NoPrint ;

     Select names

     Into :colnames

     Separated by ' '

     From namelist

     ;

Quit ;

/* create empty table */

Data temp ;

     Length Industry_IO_Code $ 8

               &colnames 8

               ;

Run ;

/* append data */

Proc SQL ;

   Create table want as

     Select * from temp (obs = 0)

          Union

     Select * from have

     ;

Quit ;

(Untested code)

Richard

Message was edited by: Richard Carson Apologies: Forgot the From clause in the first SQL

zachary_oliver
Calcite | Level 5

Thanks, Richard. This did exactly what I needed.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3134 views
  • 0 likes
  • 4 in conversation