BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LearnByMistk
Obsidian | Level 7

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 .

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

LearnByMistk
Obsidian | Level 7

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

AskoLötjönen
Quartz | Level 8

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;

Ksharp
Super User
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

Steelers_In_DC
Barite | Level 11

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);

LearnByMistk
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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