topic Re: proc sql in SAS Programming

proc sql

Sabharish — Fri, 10 Nov 2017 08:47:34 GMT

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,

🙂

Re: proc sql

Kurt_Bremser — Fri, 10 Nov 2017 09:38:24 GMT

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;

Re: proc sql

RW9 — Fri, 10 Nov 2017 09:41:36 GMT

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.

Re: proc sql

Sabharish — Fri, 10 Nov 2017 09:51:45 GMT

Thanks for the quick response.

But i need a resemblance code in proc sql for the below data step.

is this feasible.

data test;
set sashelp.class;
if age>11 then test='1';
else put 'UNOTE:' age 'less than or equal to 11';
run;

thanks in advance

Re: proc sql

Kurt_Bremser — Fri, 10 Nov 2017 09:55:23 GMT

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;

Re: proc sql

Kurt_Bremser — Fri, 10 Nov 2017 10:04:46 GMT

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.

Re: proc sql

RW9 — Fri, 10 Nov 2017 10:27:02 GMT

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.