topic Re: Automatically rename columns in SAS Enterprise Guide

Automatically rename columns

Erwinb — Mon, 01 Feb 2016 07:19:52 GMT

Hello,

I have a table that I imported from excel into EG. This table is a description of my metadata.

It contains the tablenames/fieldnames but also their 'readable' descriptions of the tables/fields.

Is there an easy to query my data to this metadata-table and automatically convert my columnnames to the descriptions?

I'm finding it difficult to describe my problem so I do hope i'm making any sense.

I have attached some screenshots.

Re: Automatically rename columns

Kurt_Bremser — Mon, 01 Feb 2016 09:06:15 GMT

I would strongly advise against using the descriptions as column names, as they are not valid Base SAS names (alphabetic characters, numbers and underlines, no special characters and blanks allowed). While this is possible, it then constantly forces you to use the 'some interesting name'n construct when accessing columns.

Instead, you should set the column labels to the descriptions, as SAS output procedures can usually be directed to show the labels instead of the names.

So, once you have your description table in SAS, you could do the following:

data _null_;
set description_table end=done;
length execute_str $ 2000;
execute_str = catx(' ',trim(execute_str),trim(fieldname)!!'="'!!trim(description)!!'"');
if done then call symput('label_str',trim(execute_str));
run;

Now you can use &label_str in a label statement.

Re: Automatically rename columns

Ksharp — Mon, 01 Feb 2016 09:13:07 GMT

I see the variable name in metadata file is not exactly matched the name in sas table .

Is there any rule to map them . and Better post some sample data to enable us to test .

Re: Automatically rename columns

RW9 — Mon, 01 Feb 2016 09:47:27 GMT

One other option to the two answers provided above, is to have a normalised dataset, with the columns as parameters. This is a common format in databases (and I am suprised to see a DB2 file with so many columns, doesn't seem like a good structure). So if you have a file like this:

VCDR_NR VEST_KD AFBO_NR...

...

What your output dataset would look like is this:

PARAM_CODE RESULT

VCDR_NR ...

VEST_KD ...

AFBO_NR ...

...

Then it should be a relatively simple task to merge your metadata on to get labels for each param:

PARAM_CODE RESULT PARAM

VCDR_NR ... Aan Vestiging

VEST_KD ... ...

AFBO_NR ... ...

You will find this structure can be easier to work with, for instance, if your doing calculations across columns, you have arrays, but you need to know and supply variable names etc. If you were to stick with the transposed approach - wide - as you have now, I would recommend, not labelling column names as data, but use a generic term, say VAR1-VARx for instance. Names of columns are to make programming easier, labels of columns are there to provide information. Make your programming far simpler by following one of the above approaches.

...

Re: Automatically rename columns

ballardw — Mon, 01 Feb 2016 15:59:00 GMT

Somewhat of a kludge, but I have been known to use a spreadsheet with column information and use that with spreadsheet functions to create a column of LABEL statements to apply to variables. Using a full description in a label makes a lot of sense as you can have more characters and characters that are not acceptable in variable names.