topic Re: how to use exists condition when querying the results from a sub-query in SAS Programming

how to use exists condition when querying the results from a sub-query

dennis_oz — Sun, 30 Jan 2022 23:25:46 GMT

hi guys,

can you please help me with the below query

data final;
	format Create_Date date9.;
	input case_id $ Create_Date Customer_Name $;
	datalines;
CAS49014 22354 kelvin
CAS50201 22667 kelvin
CAS49012 22454 james
CAS49232 22337 james
;
run;

data game;
	/*format Create_Date date9.;*/
	input  Customer_Name $ game $;
	datalines;
kelvin soccer
kelvin basketball
james soccer
james basketball
;
run;

proc sql;
	create table test99 as 

	select b.*,sb.max_create_date

	FROM game  b
		WHERE EXISTS (
			select max(Create_Date) as max_create_date format=date9. ,
				Customer_Name
			from work.final  
				group by sb.Customer_Name) sb 
					where  b.Customer_Name=sb.Customer_Name  )
	;
quit;

Error: I am getting

Please can you help with the above ERROR.

Re: how to use exists condition when querying the results from a sub-query

PaigeMiller — Mon, 31 Jan 2022 00:09:17 GMT

From the SQL documentation:

A multiple-value subquery can return more than one value from one column

Your subquery tries to return two columns:

select max(Create_Date) as max_create_date format=date9. , Customer_Name

Can't do that.

Re: how to use exists condition when querying the results from a sub-query

blueskyxyz — Mon, 31 Jan 2022 00:30:46 GMT

/*ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.*/
proc sql;
	create table test1 as 
	select b.*,sb.create_date as max_create_date 
	FROM game as  b  inner join (
                                select  
				                Customer_Name,Create_Date  from final 
                                group by  Customer_Name
                                having  max(Create_Date)=Create_Date
                                ) as sb
    on b.Customer_Name=sb.Customer_Name
	;
quit;

Re: how to use exists condition when querying the results from a sub-query

Tom — Mon, 31 Jan 2022 00:34:04 GMT

EXISTS is just for testing whether or not any observations are found in the sub-query (hence the name).

Looks you want to join with the other query instead.

proc sql;
create table test99 as 
select b.*
     , sb.max_create_date
from game  b
left join (
  select max(Create_Date) as max_create_date format=date9. 
       , Customer_Name
  from work.final  
  group by Customer_Name
  ) sb 
on  b.Customer_Name=sb.Customer_Name  
;
quit;