Capitalizing column names

[geneshackman]

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;

[Reeza]

Would labels work instead of variable names?

[Kurt_Bremser]

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

[geneshackman]

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".

[ballardw]

---

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

[geneshackman]

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.

[geneshackman]

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

[Reeza]

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. 😞

---

[mkeintz]

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;

[geneshackman]

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

[geneshackman]

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;

[Reeza]

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;

[data_null__]

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;