topic Re: better way to rename variable names based on label in SAS Programming

better way to rename variable names based on label

nnl3256 — Tue, 17 Dec 2019 21:17:26 GMT

Using SAS 9.4 TS Level 1M5. There are several tables in SQL server with table and field name longer than 32. We’ve reduced length of the table names, so such tablse can "be seen” in SAS library. But the longer field names are cut to 32 by SAS. I'm able to recognize original variable names by labels . How can I rename variables based on labels? For example Original Column Name: Patient_experience_national_com0 Column Label: Patient experience national comparison footnote New Column Name: nat_comp_ftnt Original Column Name: Efficient_use_of_medical_imagin0 Column Label: Efficient use of medical imaging national comparison footnote New Column Name: img_natcomp_ftnt ...

Re: better way to rename variable names based on label

Tom — Tue, 17 Dec 2019 21:23:03 GMT

I don't understand what you want.

If the names were changed because they were too long how do you expect to change them back to longer names?

Names can be up to 32 bytes long. Labels can be up to 256 bytes long.

Re: better way to rename variable names based on label

nnl3256 — Wed, 18 Dec 2019 15:52:41 GMT

Thank you. Basically, I am looking for a better way to automatically renaming a variable if its length over 20 characters. I guess that several steps are needed to do so.

Re: better way to rename variable names based on label

Tom — Wed, 18 Dec 2019 17:52:33 GMT

@nnl3256 wrote:

Thank you. Basically, I am looking for a better way to automatically renaming a variable if its length over 20 characters. I guess that several steps are needed to do so.

The best way is to get whoever created the crazy names to begin with to come up with names that use a normal maximum length. 20 characters sounds like a reasonable upper bound on the length of the names, hopefully most of them can use much shorter names.

The normal process that people use is to turn english phrases like you have in your labels into names is to follow something like:

Remove unneeded words like A THE AND
Replace long words with common abbreviations.
As a last resort start appending numeric suffixes to keep names distinct

Note you could also look into modifying the process for create the longer names so that they are distinct in the first XX (for example 20) characters. Then if you need to use an automatic process to truncate the names to fit into a system that does not allow paragraphs as names you don't have to worry about it causing name conflicts.

Re: better way to rename variable names based on label

ballardw — Wed, 18 Dec 2019 19:26:45 GMT

And don't forget acronyms based on recurring words in your data.

I have a data source that had Primary_Care_Giver_ and Secondary_Care_Giver followed by Last_name, First_name, Date_of_Birth, and a bunch of other characteristics.

So Primary_Care_Giver_ became PCG_ and Secondary_Care_Giver became SCG_ .

You may find this code helpful in finding likely words to abbreviate. Replace YOURLIB with the uppercase name of your Library to get a list of all of the words in your labels by frequency in the proc freq output.

Proc sql;
   create table work.labels as
   select label 
   from dictionary.columns
   where libname='YOURLIB'
   ;
run;

data work.words;
   set work.labels;
   length word $ 20;
   do i= 1 to countw(label);
      word = scan(label,i);
      output;
   end;
   drop label;
run;

Proc freq data=work.words order=freq;
   table word /nocum;
run;