DATA Step, Macro, Functions and more

Proc sql TOP equivalent

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Proc sql TOP equivalent

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.


Accepted Solutions
Solution
‎06-07-2017 03:11 PM
PROC Star
Posts: 258

Re: Proc sql TOP equivalent

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;

 

 

View solution in original post


All Replies
Solution
‎06-07-2017 03:11 PM
PROC Star
Posts: 258

Re: Proc sql TOP equivalent

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;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 146 views
  • 0 likes
  • 2 in conversation