How to rename a column in proc sql

[gsnidow]

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

[gsnidow]

This seemed to work

     somereallylongcolumnname as short_name labe='short_name'

[SASKiwi]

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]

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]

This did NOT work for me

[ballardw]

---

@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]

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]

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.