BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sabharish
Fluorite | Level 6

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,

🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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;

 

Sabharish
Fluorite | Level 6
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
Kurt_Bremser
Super User

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;
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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