Remove a format from a variable when selected in SQL

[RW9]

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?

[andreas_lds · Accepted solution]

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

[RW9]

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.

[Ksharp]

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;

[RW9]

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

[Tom]

Post it as a new feature request.

[FreelanceReinh]

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.