Desktop productivity for business analysts and programmers

best practice to rename

Reply
Regular Contributor
Posts: 220

best practice to rename

Hi all,

i have more than one dataset in the sas which has more than 1000 variables. all these variables are specific code (eg: 1234,6789, 4343, ect). I have once excelsheet which mentioned all these code (eg: 1234,6789, 4343, ect) along with variable explanation (eg: 1234=total_population).

my question is what is the best practice to implement that excel sheet into the sas dataset so that it rename 1234=total_population and with all other code to particular name?

Thanks.

Super Contributor
Posts: 543

Re: best practice to rename

When you do a proc contents on your SAS data set what are the names of your variables? (are they really 235,6789?)
I thought SAS doesn't allow for column names to be just digits:smileyconfused:

Esteemed Advisor
Posts: 7,293

Re: best practice to rename

: Actually it does if you have the validvarname option set to ANY, but I wouldn't recommend doing that.

: It would help if you tell us more and provide a brief example (i.e., NOT with 1,000 variables)

Super Contributor
Posts: 543

Re: best practice to rename

Arthur Tabachneck wrote:

Anca tilea: Actually it does if you have the validvarname option set to ANY, but I wouldn't recommend doing that.

sandip  patel: It would help if you tell us more and provide a brief example (i.e., NOT with 1,000 variables)

Thank you,Arthur.

Here is a non SAS question: how do I reference someone in my reply (like you just did)??

Esteemed Advisor
Posts: 7,293

Re: best practice to rename

Start by typing the @ symbol, then immediately follow the symbol by typing the person's screen name.  Doesn't work for all names, but does for most.

Super Contributor
Posts: 543

Re: best practice to rename

:smileylaugh:

Grand Advisor
Posts: 17,332

Re: best practice to rename

If its in an excel sheet I typically create a new column in excel using functions that looks like this:

oldname1=newname1

oldname2=newname2

And then copy and paste that into SAS code with the rename statement.

So I add a rename before the names and a semi colon afterwards.

There are more automated solutions but this works as well.

Esteemed Advisor
Posts: 5,198

Re: best practice to rename

I would say that best practice (in most scenarios) is not to have tables with hundred/thousands of columns. I quite positive it will be easier for you if you transpose your data, and then you can use standard table lookup techniques, like joins, merge, user defined formats etc to apply descriptions to your codes.

Data never sleeps
Regular Contributor
Posts: 220

Re: best practice to rename

@Arthur - sir, i have variable like

P002001

P002002

P002003

P002004

and many more lwith this kind of code - wanted to change to

P002001=tot_urban_rural

P002002=tot_urban

P002003=inside_urbanized_areas

P002004=inside_urban_clusters

i have excel sheet with code (P002001) and actual variable name (tot_urban_rural) that i wants to rename it...

Thanks all for your feedback

Grand Advisor
Posts: 17,332

Re: best practice to rename

You rename variables in SAS using either proc datasets or in a data step.

In a datastep you are creating new data set whereas in proc datasets you're modifying your original dataset. Generally how you get variables listed is the issue. If you only need to do it once then typing it in, or copying it from excel is fine. If you need to do it multiple times, then I agree with Linus, the structure of your data is not optimal.  However, you can do it using macro variables. It's been illustrated many times on this forum so some searching should help you find it.

In a dataset:

data want;

set have;

rename

P002001=tot_urban_rural

P002002=tot_urban

P002003=inside_urbanized_areas

P002004=inside_urban_clusters;

run;

In proc datasets:

proc datasets library=work;

modify have;

rename

P002001=tot_urban_rural

P002002=tot_urban

P002003=inside_urbanized_areas

P002004=inside_urban_clusters;

run;quit;

Esteemed Advisor
Posts: 7,293

Re: best practice to rename

: Here is an example using Fareeza's suggested code:

data spreadsheet;

  informat name $32.;

  input var $ name;

  cards;

P002001 tot_urban_rural

P002002 tot_urban

P002003 inside_urbanized_areas

P002004 inside_urban_clusters

;

proc sql noprint;

  select strip(var)||'='||strip(name)

    into :renames separated by " "

      from spreadsheet

  ;

quit;

data have;

  input p002001 p002002 p002003 p002004;

  cards;

1 2 3 4

5 6 7 8

;

data have;

  set have;

  rename &renames.;

run;

proc datasets library=work;

  modify have;

  rename &renames.;

quit;

Ask a Question
Discussion stats
  • 10 replies
  • 361 views
  • 1 like
  • 5 in conversation