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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.