Help using Base SAS procedures

Proc sql issue

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Proc sql issue

Hi ,

i need to figure out few steps with in a same or 2 queries

data i have

drp_cnt      blk_cnt      cell site

23               43                 A

132             34                 B

154              63                C

So i want to figure out average of all drop_cnt  as (103) and blk_cnt as (46.67)   .. this average is not depending on cell site but a genral average of whole count (drop n block).

data want

drp_cnt      blk_cnt      cell site  drp_flg      blk_flg

23               43                 A          1          1

132             34                 B          0          1

154              63                C          0          0

Here drp_flg and blk_flg ...i create depending upon the average values of drop n blk count ie  drop_cnt  as (103) and blk_cnt as (46.67)...so if value is > avg its 0 else 1 .

I tried to resolve this using calculated staement in proc sql but dont get the  required result .

Your help is greatly appreciated .


Accepted Solutions
Solution
‎05-20-2015 08:00 AM
Super User
Super User
Posts: 7,979

Re: Proc sql issue

Posted in reply to LearnByMistk

Something like:

data have;

  drp_cnt=23; blk_cnt=43; cell="A"; output;

  drp_cnt=132; blk_cnt=34; cell="B"; output;

  drp_cnt=154; blk_cnt=63; cell="C"; output;

run;

proc sql;

  create table WANT as

  select A.*,

         case when B.AVG1 > A.DRP_CNT then 1 else 0 end as DRP_FLG,

         case when B.AVG2 > A.BLK_CNT then 1 else 0 end as BLK_FLG

  from   HAVE A

  left join (select AVG(DRP_CNT) as AVG1, AVG(BLK_CNT) as AVG2 from HAVE) B

  on     1=1;   /* Note, there isn't a grouping you have given, so I just merge the result value to everything.  This isn't advised, but with the small example should work */

quit;

View solution in original post


All Replies
Solution
‎05-20-2015 08:00 AM
Super User
Super User
Posts: 7,979

Re: Proc sql issue

Posted in reply to LearnByMistk

Something like:

data have;

  drp_cnt=23; blk_cnt=43; cell="A"; output;

  drp_cnt=132; blk_cnt=34; cell="B"; output;

  drp_cnt=154; blk_cnt=63; cell="C"; output;

run;

proc sql;

  create table WANT as

  select A.*,

         case when B.AVG1 > A.DRP_CNT then 1 else 0 end as DRP_FLG,

         case when B.AVG2 > A.BLK_CNT then 1 else 0 end as BLK_FLG

  from   HAVE A

  left join (select AVG(DRP_CNT) as AVG1, AVG(BLK_CNT) as AVG2 from HAVE) B

  on     1=1;   /* Note, there isn't a grouping you have given, so I just merge the result value to everything.  This isn't advised, but with the small example should work */

quit;

Contributor
Posts: 28

Re: Proc sql issue

Posted in reply to LearnByMistk

Ok let me check on my given data than i revert but really thanks for so prompt reply ..

Contributor
Posts: 44

Re: Proc sql issue

Posted in reply to LearnByMistk

proc sql;

create table want as

select drp_cnt,

        blk_cnt,

        cell_site,

        case when drp_cnt < mean(drp_cnt) then 1

             when drp_cnt > mean(drp_cnt) then 0 end as drp_flg,

        case when blk_cnt < mean(blk_cnt) then 1

             when blk_cnt > mean(blk_cnt) then 0 end as blk_flg

from   have;

;

quit;

Super User
Posts: 10,041

Re: Proc sql issue

Posted in reply to LearnByMistk
data have;
input drp_cnt      blk_cnt      cell_site $;
cards;
23               43                 A
132             34                 B
154              63                C
;
run;
proc sql;
create table want(drop=m1 m2) as
 select * ,mean(drp_cnt) as m1,mean(blk_cnt) as m2,(drp_cnt lt calculated m1) as f1,(blk_cnt lt calculated m2) as f2
  from have;
quit;


Xia Keshan

Valued Guide
Posts: 860

Re: Proc sql issue

Posted in reply to LearnByMistk

Here's a shout out to Black Flag:

data have;

infile cards dsd dlm=',';

input drp_cnt blk_cnt cell_site $;

cards;

23,43,A

132,34,B

154,63,C

;

run;

proc sql;

create table want as

select *,

case

when blk_cnt > avg_blk_cnt then 0

    else 1

    end as blk_flg,

case

when drp_cnt > avg_drp_cnt then 0

    else 1

    end as drp_flg from(

select *,avg(drp_cnt) as avg_drp_cnt,avg(blk_cnt) as avg_blk_cnt format=number7.2

from have);

Contributor
Posts: 28

Re: Proc sql issue

Posted in reply to LearnByMistk

Thanks all of you for your help * Support  ..that's the only reason i love this community

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 296 views
  • 0 likes
  • 5 in conversation