BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASdevAnneMarie
Barite | Level 11

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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
Barite | Level 11

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.

 

 

SASdevAnneMarie_0-1661945188460.png

 

PeterClemmensen
Tourmaline | Level 20

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
Barite | Level 11
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
Barite | Level 11

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
Barite | Level 11

Thank you Peter !

Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1821 views
  • 2 likes
  • 3 in conversation