I have the following code:
proc sql;
UPDATE TEMP
SET CUST_NATIONALITY=(SELECT CUST_NATIONALITY
FROM (SELECT CUST_NATIONALITY, COUNT(CUST_NATIONALITY) AS FREQ
FROM MIP_LIB.REATIL_PORTFOLIO_NEW
GROUP BY CUST_NATIONALITY)
HAVING FREQ=MAX(FREQ))
WHERE CUST_NATIONALITY IS NULL;
quit;
The subquery is returning more than one value. Any one of the value will do for me. Can anyone suggest how to do this with the minimum change made, as I have several such queries throughout my project.
I think, simplest is to make one record table and then update. Like something below
proc sql outobs= 1; create table temp_stage as SELECT CUST_NATIONALITY FROM (SELECT CUST_NATIONALITY, COUNT(CUST_NATIONALITY) AS FREQ FROM MIP_LIB.REATIL_PORTFOLIO_NEW GROUP BY CUST_NATIONALITY) HAVING FREQ=MAX(FREQ)) WHERE CUST_NATIONALITY IS NULL; quit; proc sql; UPDATE TEMP SET CUST_NATIONALITY=(select cust_nationality from temp_stage);; quit;
I think, simplest is to make one record table and then update. Like something below
proc sql outobs= 1; create table temp_stage as SELECT CUST_NATIONALITY FROM (SELECT CUST_NATIONALITY, COUNT(CUST_NATIONALITY) AS FREQ FROM MIP_LIB.REATIL_PORTFOLIO_NEW GROUP BY CUST_NATIONALITY) HAVING FREQ=MAX(FREQ)) WHERE CUST_NATIONALITY IS NULL; quit; proc sql; UPDATE TEMP SET CUST_NATIONALITY=(select cust_nationality from temp_stage);; quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.