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

[wcp_fnfg]

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?

[RW9]

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;

[wcp_fnfg]

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.

[BrunoMueller]

Hi

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

[BrunoMueller]

Hi

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