SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Frequent Contributor
Posts: 84

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

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?

Super User
Super User
Posts: 7,392

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

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;

Frequent Contributor
Posts: 84

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

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.

SAS Super FREQ
Posts: 682

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

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
;;;;
SAS Super FREQ
Posts: 682

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

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



Ask a Question
Discussion stats
  • 4 replies
  • 400 views
  • 3 likes
  • 3 in conversation