BookmarkSubscribeRSS Feed
gsnidow
Obsidian | Level 7

Greetings all.  I'm having some difficulty renaming a column in proc sql.  Consider the following:

proc sql;

     create table sometable as

          select

               '0' as group_num,

               somereallylongcolumnname as short_name

            from sourcetable;

quit;

When I run this code, the first column is correctly named, as it should have been based on some documentation I found on the web.  However, the column 'somereallylongcolumnname' retains its name eventhough I used an alias.  I read where the alias will be displayed on reports and such, but I need the column to be renamed in the dataset.  I tried using (rename= at various places, but none seem to work.  I'm sure it is something simple, but I just can't get it.  Any ideas?  Thank you.

Greg

7 REPLIES 7
gsnidow
Obsidian | Level 7

This seemed to work

     somereallylongcolumnname as short_name labe='short_name'

SASKiwi
PROC Star

Seems like you might be confusing column names with column labels. If you are sourcing your data from an external database often the label will be set to the same as the column name. The label is what is often used in reports. You could try the NOLABEL SAS option to avoid assigning labels when importing data.

gsnidow
Obsidian | Level 7

SASKiwi, thank you for the reply.  I'm not concerned about the label, its just that is the only way I could get the column to be named as I want.  My concern is that I need to use this dataset in some other stuff, and I would like to be able to reference the alias I named in the proc sql statement.  It works not, so I'm not sure if it's a label or the column name is correct, but I can reference short_name in the dataset.  Thanks.

mscarbro
Calcite | Level 5

This did NOT work for me

ballardw
Super User

@mscarbro wrote:

This did NOT work for me


Then start your own thread and show what you did. Piggybacking on a 10 year old thread is not encouraged on this forum. If you think your issue is close to another than feel free to post a link to this one but be prepared to discuss what you looked at to decide your attempt did not succeed. Please note that the code by the OP in this thread would not change the LABEL associated with variable if there had been one and many procedures and data views default to showing a label when one is defined.

Run Proc Contents on your data set and share the result.

You should be able to run this code and follow the results. This copies a SAS supplied data set that you should have in your SASHELP library, adding labels to some of the variables, runs proc contents to show the result, uses proc sql to create another data set and change the  names of some variables and then runs proc contents on that new set. See how the LABEL values are still there? I suspect if you look at the created work.cl data set using the same way you examined your "didn't work" set you will see the label even though the variable names did actually change.

data work.class; 
   set sashelp.class;
   label 
   name='Student name'
   sex ='Student sex'
   age ='Age and enrollment'
   ;
run;

proc contents data=work.class;
run;

proc sql;
   create table work.cl as 
   select name as NowHasAVeryLongName,
          sex as gender
   from work.class
   ;
quit;

proc contents data=work.cl;
run;

 

"Did not work" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

Anotherdream
Quartz | Level 8

I agree with Kiwi. In your original code you actually DID rename the column, however you were still displaying its label of "somereallylongcolumnname", therefore when you open your dataset this is what you see (you acutally see the label and not the column name in the column header. You can see both if you go to the column properties)!

hope that helps

Reeza
Super User

You would be able to reference it as short_name from your original step as well. It isn't an alias though, its a rename.

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
  • 7 replies
  • 12002 views
  • 1 like
  • 6 in conversation