Having in proc sql

[SASdevAnneMarie]

Hello Experts,

In the data RGA I have the multiple values of d_fin by is_support.

d_fin is the date of creation, I need to choose the most recent or with the empty value.

I try to use "having" but I have the syntax error : 

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Could you help me please ?

proc sql;
	create table Donnees as select  c.cd_rga, b.is_support, c.lb_court, b.d_fin,a.mt_ea,
		sum(a.mt_ea) as total	
	from SUPPORT as a
		left join RGA as b on a.is_support=b.is_support and (having b.d_fin=. or max(b.d_fin)=b.d_fin)
		left join RGPC as c on b.is_rga=c.is_rga
			where a.s_type_support="OK"
				group by c.cd_rga
	;
quit;

Thank you !

[PeterClemmensen · Accepted solution]

This is untested since I can't see your data. But I think you want something like this

proc sql;
	create table Donnees as 
   select  c.cd_rga
         , b.is_support
         , c.lb_court
         , b.d_fin
         , a.mt_ea,
	      , sum(a.mt_ea) as total	
	from SUPPORT as a

   left join (select is_support
              from RGA
              group by is_support
              having b.d_fin = . 
              or max(b.d_fin) = b.d_fin)  as b
   on a.is_support = b.is_support

	left join RGPC as c 
   on b.is_rga=c.is_rga

	where a.s_type_support="OK"
	group by c.cd_rga
	;
quit;

[SASdevAnneMarie]

Thank you Peter,

Unfortunately it doesn't work in case when I have the empty vaue en non-empty value for the same IS_SUPPORT.

In my case I need to tae the empty value. The code take the two values.

Thank you for your help.

[PeterClemmensen]

Since you did not provide any sample data, I'm kind of shooting in the dark here.

Try changing the inner query to

select distinct is_support
from RGA
group by is_support
having max(b.d_fin) = b.d_fin

[SASdevAnneMarie]

Thank you, Peter, but it doesn't work,beacause the result will be 08Sept2013 for IS_SUPPORT 137863.
In case whe I have the empty value, I need to choose the empty value and not the max.

[SASdevAnneMarie]

I would like to use max(b.d_fin) = b.d_fin and replace the empty value with "31DEC2023"d but it doesn't work, I have the value 01/01/1960 and not 31/12/2023.

Could you please explain me how to write the case statement ? 

proc sql;
	create table Donnees as 
   select  c.cd_rga
         , b.is_support
         , c.lb_court
         , b.d_fin
         , a.mt_ea,
	      , sum(a.mt_ea) as total	
	from SUPPORT as a

   left join (select is_support,
    case d_fin when . then d_fin="31DEC2023"d
             end as d_fin format ddmmyy10.
              from RGA
              group by is_support
              having 
              or max(b.d_fin) = b.d_fin)  as b
   on a.is_support = b.is_support

	left join RGPC as c 
   on b.is_rga=c.is_rga

	where a.s_type_support="OK"
	group by c.cd_rga
	;
quit;

Thank you !

[SASdevAnneMarie]

Thank you Peter !

[Kurt_Bremser]

HAVING is a clause and can therefore not be part of a condition.

Please post usable (data steps with datalines) examples for your datasets, and what you expect to get out of them with your query.