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.
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.
/*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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.