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?
There seems to be no way to remove a format with proc sql ,except the one you already found.
There seems to be no way to remove a format with proc sql ,except the one you already found.
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.
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;
Thanks. Its not what I am after though, I would like to remove a format completely, without an additional step.
Post it as a new feature request.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.