topic Proc sql TOP equivalent in SAS Programming

Proc sql TOP equivalent

Bipasha — Wed, 07 Jun 2017 12:49:36 GMT

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.

Re: Proc sql TOP equivalent

kiranv_ — Wed, 07 Jun 2017 13:32:36 GMT

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;