proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

proc sql

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,

:-)

 


Accepted Solutions
Solution
‎11-10-2017 05:01 AM
Super User
Posts: 8,037

Re: proc sql

Posted in reply to Sabharish

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 8,037

Re: proc sql

Posted in reply to Sabharish

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 16

Re: proc sql

Posted in reply to KurtBremser
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
Solution
‎11-10-2017 05:01 AM
Super User
Posts: 8,037

Re: proc sql

Posted in reply to Sabharish

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 8,037

Re: proc sql

Posted in reply to Sabharish

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 8,152

Re: proc sql

Posted in reply to Sabharish

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.

Super User
Super User
Posts: 8,152

Re: proc sql

Posted in reply to Sabharish

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 111 views
  • 0 likes
  • 3 in conversation