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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.