BookmarkSubscribeRSS Feed
sas_9
Obsidian | Level 7

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.

10 REPLIES 10
AncaTilea
Pyrite | Level 9

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:

art297
Opal | Level 21

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

AncaTilea
Pyrite | Level 9

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

art297
Opal | Level 21

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.

AncaTilea
Pyrite | Level 9

:smileylaugh:

Reeza
Super User

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.

LinusH
Tourmaline | Level 20

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
sas_9
Obsidian | Level 7

@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

Reeza
Super User

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;

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1162 views
  • 1 like
  • 5 in conversation