topic Re: Remove a format from a variable when selected in SQL in SAS Programming

Remove a format from a variable when selected in SQL

RW9 — Thu, 18 Oct 2018 12:29:06 GMT

Has anyone had this scenario and found a fix. I have a variable named age coming in from a dataset which has format=4.

I have a proc sql which creates a new dataset and selects this variable. In this instance the format is copied across. Now when a variable is processed in SQL, and no format is applied to the outgoing variable, then the format is missing. Here is an example:

data have;
  attrib age format=4. length=8;
  age=10;
run;

proc sql;
  create table want as
  select age,
         sum(age,0) as want_age
  from   have;
quit;

If you run this you will see age retains the 4. format, but want_age does not have the 4. format applied. I am after the syntax to remove format when I am not processing the variable - as summing 0 in this case should be ok, but would like to avoid processing. Have tried:

create table want (format _all_)...

age format=,

Just can't seem to put my finger on the right syntax (if indeed there is one). I am aware that in datastep you can do format _all_, or proc datasets, but the question is can I do it in my existing proc sql step without needing a further step, or is fooling the compiler into creating a new variable the only way?

Re: Remove a format from a variable when selected in SQL

andreas_lds — Thu, 18 Oct 2018 13:07:17 GMT

There seems to be no way to remove a format with proc sql ,except the one you already found.

Re: Remove a format from a variable when selected in SQL

RW9 — Thu, 18 Oct 2018 13:13:29 GMT

Ok, thanks. I had thought that might be the case. Its a tad annoying to have to resort to a function call or some logic to avoid, but I suppose its an optional component of the language, not really a SAS part.

Re: Remove a format from a variable when selected in SQL

Ksharp — Thu, 18 Oct 2018 14:00:11 GMT

SQL can modify format,But I don't know if it is what you are looking for.


proc sql;
  create table want as
  select age,
         sum(age,0) as want_age
  from   have;
  alter table want
   modify age format=best32. ;
quit;

Re: Remove a format from a variable when selected in SQL

RW9 — Thu, 18 Oct 2018 14:02:06 GMT

Thanks. Its not what I am after though, I would like to remove a format completely, without an additional step.

Re: Remove a format from a variable when selected in SQL

Tom — Thu, 18 Oct 2018 14:10:05 GMT

Post it as a new feature request.

Re: Remove a format from a variable when selected in SQL

FreelanceReinh — Thu, 18 Oct 2018 14:36:05 GMT

Good question.

Slightly shorter:

select age+0 as age

(as was suggested by Tom in this 2015 thread: SAS sql remove format and by Howard Schreier -- author of the book "PROC SQL by Example" -- in a 2007 discussion on SAS-L: Remove a format via proc sql, where he also suggested trim(varname) as varname for character variables). Others suggested assigning a "neutral" format such as BEST. or $F. in these old discussions.