Hi All,
While using case when statement in proc sql. when it doesn't satisfy the case when statement. I need to throw a UNOTE in log.
Can someone help me with this.
Thanks in Advance,
🙂
Just expand the argument for the resolve function:
proc sql;
create table test as
select
name,
weight,
case
when age > 11 then '1'
else resolve('%put UNOTE: ' !! strip(put(age,best.)) !! ' less than or equal to 11;')
end as test
from sashelp.class;
quit;
You can use the resolve() function and the %put macro statement:
proc sql;
create table test as
select
name,
weight,
case
when age > 11 then '1'
else resolve('%put error!;')
end as test
from sashelp.class;
quit;
Just expand the argument for the resolve function:
proc sql;
create table test as
select
name,
weight,
case
when age > 11 then '1'
else resolve('%put UNOTE: ' !! strip(put(age,best.)) !! ' less than or equal to 11;')
end as test
from sashelp.class;
quit;
You know what the funny thing is?
Until today, I had no idea that something like the resolve() function exists. But I read this topic (just three posts down from yours on the "All Topics" list), and there found a reference to resolve(), which made me investigate further.
I do have to ask why. There is one, and only one reason why you would need to use SQL, and that is if you are passing the SQL code through to a database. If that is the case then you would not be able to use SAS functionality anyways. So your question does not make sense. Use SAS, for which you already have the code written. There is no reason to change it to SQL, and then put in place - what frankly is another really bad use of the macro processor.
There isn't (as far as I can figure out) a way to put text out to the log. This is because the SQL parser is a separate entity from the Base SAS system, so the function like put is not available. I would put a step like this after your SQL:
data _null_; set <yourdata> (where=(VAR not in (<list of valid values)) obs=1; put UNOTE: Incorrect value found; run;
Not tested, so you would need to fiddle around, but basically the data null should read 1 record from the data where the list is not in the prescribed list, and if there is one record then put the log message out. Or you could simply do your processing in a datastep straight up, no need to use SQL.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.