topic Re: Convert "SQL" style name (hyphens, etc) to SAS? in SAS Data Management

Convert "SQL" style name (hyphens, etc) to SAS?

wcp_fnfg — Mon, 27 Apr 2015 14:57:01 GMT

My input data has things like spaces and dashes in a SQL table, is there a quick way to convert to SAS names, rather than doing the whole thing like

a."lt-acnt"n as lt_acnt,

a."new-mtg-nbr"n as new_mtg_nbr,

etc?

Re: Convert "SQL" style name (hyphens, etc) to SAS?

RW9 — Mon, 27 Apr 2015 15:20:08 GMT

Well, the options validavarname=any; will allow you to work with these:

options validvarname=any;

data have;

"lt-acnt"n="abc"; output;

run;

You can then easily post-process the metadata something like:

data _null_;

set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;

if _n_=1 then call execute('data want; set have (rename=(');

call execute("'"||strip(name)||"'n="||strip(tranwrd(name,"-","_")));

if last then call execute('));run;');

run;

Re: Convert "SQL" style name (hyphens, etc) to SAS?

BrunoMueller — Mon, 27 Apr 2015 15:21:20 GMT

How where the original SAS Data Sets created? Using the Import task from EG or Proc Import or ...

If you are using SAS Enterprise Guide, it will set the SAS System Option VALIDVARNAME=ANY. This might be the cause of the names you are seeing.

You can use options validvarname=v7; so that Proc IMPORT creates names with underscores at places where the original name had blanks

Re: Convert "SQL" style name (hyphens, etc) to SAS?

wcp_fnfg — Mon, 27 Apr 2015 15:23:31 GMT

That's a decent idea, using the dictionary tables.

I don't create the data, so using vvn=v7 to convert on the input is not an option.

Re: Convert "SQL" style name (hyphens, etc) to SAS?

BrunoMueller — Mon, 27 Apr 2015 16:17:03 GMT

In case this is of any help, the code sample below will translate all sorts of characters which are not valid for a SAS Name into an underscore. Beside that I would recommend Proc DATASETS with the RENAME statement, this way you avoid reading the again.

data test;
  infile cards truncover; 
  input
    name $50.
  ;
  name2 = name; 
  if find("0123456789", char(name2, 1)) then do; 
    substr(name2,1,1) = "_"; 
  end; 
  name2 = basechar(name2);

  name2 = prxchange("s/[^a-z¦A-Z¦0-9¦_]/_/", -1, strip(name2));
cards4; 
übergang
valid_name
lt-acnt
1 year
some name
a.id
;;;;