Proc sql TOP equivalent

[Bipasha]

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.

[kiranv_ · Accepted solution]

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;