topic Capitalizing column names in SAS Programming

Capitalizing column names

geneshackman — Fri, 05 Mar 2021 17:51:01 GMT

Hi all

I have a data set. Some of the columns have names that are not capitalized, but I want them capitalized. In particular, I want to change "data_source" to "Data_Source", but rename doesn't seem to work. I'm attaching a small version of the data set, just so it won't take up much space, and so it's easier to work with. I'm using SAS 9.4 TS Level 1M6 on a windows desktop. What am I doing wrong?

data testtest2;
set testtest;
rename data_source=Data_Source;
run;

Re: Capitalizing column names

Reeza — Fri, 05 Mar 2021 18:06:10 GMT

Would labels work instead of variable names?

Re: Capitalizing column names

Kurt_Bremser — Fri, 05 Mar 2021 18:20:12 GMT

If you want something fancy, put it into labels. Upper/lowercase is irrelevant with regards to variable names.

Re: Capitalizing column names

geneshackman — Fri, 05 Mar 2021 18:35:11 GMT

Forgot to say, the reason I need to change to upper case is that I will be using this data set in tableau, and in tableau, upper case and lower case matter. Tableau doesn't think that "data_source" is the same variable as is "Data_Source".

Re: Capitalizing column names

mkeintz — Fri, 05 Mar 2021 18:51:14 GMT

A rename statement won't change case, but a rename dataset-name parameter will.

I.e., instead of:

data testtest2;
set testtest;
rename data_source=Data_Source;
run;

use:

data testtest2 (rename=(data_source=Data_Source));
  set testtest;
run;

But even better don't bother with copying the dataset. Change the original dataset in place via proc datasets:

proc datasets library=work nolist ;
  modify testtest;
  rename data_source=Data_Source;
quit;

Re: Capitalizing column names

ballardw — Fri, 05 Mar 2021 19:39:27 GMT

@geneshackman wrote:
Forgot to say, the reason I need to change to upper case is that I will be using this data set in tableau, and in tableau, upper case and lower case matter. Tableau doesn't think that "data_source" is the same variable as is "Data_Source".

My suggestion is to go back to the step that creates the variables in the first place and make sure that you have what you want.

Then you don't have to "fix" anything.

Are you pointing Tableau to the SAS data set or exporting the data from SAS and then reading into Tablea?

Re: Capitalizing column names

geneshackman — Fri, 05 Mar 2021 19:45:52 GMT

Thanks for the suggestions. I tried them, but unfortunately, didn't work, for this particular data set anyway.

Re: Capitalizing column names

geneshackman — Fri, 05 Mar 2021 19:47:14 GMT

Yes, I tried that too, to go back to when they were created. I'm having some difficulty there too. I think I'm pointing tableau to the sas data set.

Re: Capitalizing column names

geneshackman — Fri, 05 Mar 2021 20:19:19 GMT

Yes, I eventually did that, and got it to have the right column names. I don't know why rename didn't work. 😞

Re: Capitalizing column names

Reeza — Fri, 05 Mar 2021 20:54:26 GMT

Rename doesn't work because SAS doesn't care about cases for variable names.
When connecting to Tableau are you using a SAS data set that your Tableau Server is connecting to?

One brute force way to always get this, is to create a table using the SQL statement and then insert/append your data in each time.

If the create table code is the same each time you should be good to go from there in my experience.

proc sql;
   create table paylist
       (IdNum char(4),
        Gender char(1),
        Jobcode char(3),
        Salary num,
        Birth num informat=date7.
                  format=date7.,
        Hired num informat=date7.
                  format=date7.);
quit;

proc append base=paylist data=yourData;
run;

@geneshackman wrote:
Yes, I eventually did that, and got it to have the right column names. I don't know why rename didn't work. 😞

Re: Capitalizing column names

geneshackman — Fri, 05 Mar 2021 21:04:15 GMT

Hi all

I eventually used a label statement, that seemed to work. Thanks everyone for the suggestions.

data testtest2;
label Ind_id= Title= Year= Group= Characteristics= rate_percent= Low= High= Axis= Objective= rate_percent_ci= rate_percent_tx= rev_color= Data_Source= ;
set testtest;
run;

Re: Capitalizing column names

data_null__ — Fri, 05 Mar 2021 21:08:18 GMT

You can do it with RENAME dataset option. You can also used RETAIN before SET.

data class;
   retain AGE;
   set sashelp.class(rename=(name=NaMe height=HeiGHT));
   run;
proc contents varnum;
   run;

Re: Capitalizing column names

Reeza — Fri, 05 Mar 2021 21:13:08 GMT

This will remove all variable labels.
You can also just use the attrib statement within the data step as well.

proc datasets library=mylib nolist;
modify mydataset;
attrib _all_ label='';
quit;