topic Re: best practice to rename in SAS Enterprise Guide

best practice to rename

sas_9 — Fri, 01 Feb 2013 18:33:34 GMT

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.

Re: best practice to rename

AncaTilea — Fri, 01 Feb 2013 18:52:42 GMT

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:

Re: best practice to rename

art297 — Fri, 01 Feb 2013 19:13:24 GMT

: 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)

Re: best practice to rename

AncaTilea — Fri, 01 Feb 2013 19:15:29 GMT

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

Re: best practice to rename

art297 — Fri, 01 Feb 2013 19:20:32 GMT

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.

Re: best practice to rename

AncaTilea — Fri, 01 Feb 2013 19:21:50 GMT

:smileylaugh:

Re: best practice to rename

Reeza — Fri, 01 Feb 2013 20:05:55 GMT

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.

Re: best practice to rename

LinusH — Fri, 01 Feb 2013 20:17:37 GMT

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.

Re: best practice to rename

sas_9 — Mon, 04 Feb 2013 16:25:46 GMT

@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

Re: best practice to rename

Reeza — Mon, 04 Feb 2013 16:31:49 GMT

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;

Re: best practice to rename

art297 — Mon, 04 Feb 2013 17:13:29 GMT

: 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;