sub-query with order by

Reply
Occasional Contributor
Posts: 5

sub-query with order by

HI,

I can not update the record while using sub-query with order by.

proc sql;

update mkt.io_campaign_temp set n_type='TARGET', flag=1 where subs_id in

(select x.subs_id from mkt.control x where x.campaign_id="1234" and monotonic() le 10 order by w2_rev,w3_rev,w4_rev,w1_rev desc);

run;

Thanks,

Trusted Advisor
Posts: 1,137

Re: sub-query with order by

Posted in reply to ebad_shakeeb

Hope you tried

proc sql;

update mkt.io_campaign_temp set n_type='TARGET', flag=1 where subs_id in

(select x.subs_id from mkt.control x where x.campaign_id="1234" and monotonic() le 10 ) order by w2_rev,w3_rev,w4_rev,w1_rev desc;

run;

Thanks,
Jag
Respected Advisor
Posts: 4,923

Re: sub-query with order by

Posted in reply to ebad_shakeeb

Using Monotonic() is just asking for trouble. Try instead :

proc sort data=mkt.control out=controlSort;

by campaign_id w2_rev w3_rev w4_rev w1_rev desc;

run;

data controlSort;

set controlSort; by campaign_id;

if first.campaign_id then order = 0;

order + 1;

run;

proc sql;

update mkt.io_campaign_temp

set

  n_type='TARGET',

  flag=1

  where subs_id in

  (select subs_id from controlSort where campaign_id="1234" and order le 10);

drop table controlSort;

quit;

PG

PG
N/A
Posts: 1

Re: sub-query with order by

I agree with PGStats...much cleaner.  Try that.

Ask a Question
Discussion stats
  • 3 replies
  • 307 views
  • 0 likes
  • 4 in conversation