Nested query of sum for grouped average and replacing all missing,null values

Accepted Solution Solved
Reply
Contributor BSL
Contributor
Posts: 25
Accepted Solution

Nested query of sum for grouped average and replacing all missing,null values

Dear Experts,

I have a data looks like it mentioned below. In this data I want to get the sum of grouped average at the last grouped date like mentioned in WANT data and replacing all NULL and missing values to zeros.

So can we achieve it using SAS?????

HAVE*********************************

DATE      SHIFT   IPRN  QTY SALE

05Jan2014 Day     12     1   55

05Jan2014 Day     11     3   44

05Jan2014 Day     10     4   33

05Jan2014 Day     .       8   27

05Jan2014 Night   07     1   54

05Jan2014 Night   07     6   43

05Jan2014 Night   NULL 2   14

06Jan2014 Day     08     6   23

06Jan2014 Day     12     5   12

06Jan2014 Day     NULL 1   26

06Jan2014 Night   07     8   41

06Jan2014 Night   .       6   21

06Jan2014 Night   09     4   15

06Jan2014 Night   11     1   38

WANT*********************************

DATE      SHIFT   IPRN  QTY SALE

05Jan2014 Day     00    8   27

06Jan2014 Night   00    6   21

05Jan2014 Night   00    2   14

06Jan2014 Day     00    1   26

05Jan2014 Day     00    1   55

05Jan2014 Day     00    3   44

05Jan2014 Day     18    4   33

06Jan2014 Night   00    1   54

06Jan2014 Night   00    6   43

06Jan2014 Day     00    6   23

06Jan2014 Day     00    5   12

06Jan2014 Night   00    8   41

06Jan2014 Night   00    4   15

06Jan2014 Night   19    1   38

Regards

Rahul


Accepted Solutions
Solution
‎11-13-2014 08:15 AM
Super User
Posts: 9,691

Re: Nested query of sum for grouped average and replacing all missing,null values

But your data is not what it looks like:

06Jan2014 Night   00    1   54 <- should be 05Jan2014

06Jan2014 Night   00    6   43 <- should be 05Jan2014

data have;

input DATE  :date9.    SHIFT $  IPRN $ QTY SALE;

format date date9.;

cards;

05Jan2014 Day     12     1   55

05Jan2014 Day     11     3   44

05Jan2014 Day     10     4   33

05Jan2014 Day     .       8   27

05Jan2014 Night   07     1   54

05Jan2014 Night   07     6   43

05Jan2014 Night   NULL 2   14

06Jan2014 Day     08     6   23

06Jan2014 Day     12     5   12

06Jan2014 Day     NULL 1   26

06Jan2014 Night   07     8   41

06Jan2014 Night   .       6   21

06Jan2014 Night   09     4   15

06Jan2014 Night   11     1   38

;

run;

data temp missing;

set have;

ip=input(IPRN,?? best32.);

if missing(ip) then do;ip=0;output missing;end;

  else output temp;

drop IPRN ;

run;

proc sql;

create table x as

select a.*,b.sum

  from temp as a left join (select date,sum(m) as sum from (select date,avg(ip) as m from temp group by date,shift) group by date) as b

   on a.date=b.date;

quit;

data x;

set x;

by date;

if last.date then iprn=sum;

  else iprn=0;

drop sum ip;

run;

data want;

set missing(drop=ip) x;

if missing(iprn) then iprn= 0;

run;

Xia Keshan

View solution in original post


All Replies
Frequent Contributor
Posts: 144

Re: Nested query of sum for grouped average and replacing all missing,null values

In your HAVE dataset, you have NULL and "." in the same columns, so this column will have character format. The first step to calculate over this var is changed to a numeric one replacing NULL and "." by 0 like you want.

Then to compute the mean by groups you can do an sql

proc sql;

create table WANT as (

select DATE, SHIFT, mean(IPRN ) as IPRN,  mean(QTY ) as QTY ,  mean(SALE) as SALE

from HAVE

group by DATE, SHIFT

);

quit;

Solution
‎11-13-2014 08:15 AM
Super User
Posts: 9,691

Re: Nested query of sum for grouped average and replacing all missing,null values

But your data is not what it looks like:

06Jan2014 Night   00    1   54 <- should be 05Jan2014

06Jan2014 Night   00    6   43 <- should be 05Jan2014

data have;

input DATE  :date9.    SHIFT $  IPRN $ QTY SALE;

format date date9.;

cards;

05Jan2014 Day     12     1   55

05Jan2014 Day     11     3   44

05Jan2014 Day     10     4   33

05Jan2014 Day     .       8   27

05Jan2014 Night   07     1   54

05Jan2014 Night   07     6   43

05Jan2014 Night   NULL 2   14

06Jan2014 Day     08     6   23

06Jan2014 Day     12     5   12

06Jan2014 Day     NULL 1   26

06Jan2014 Night   07     8   41

06Jan2014 Night   .       6   21

06Jan2014 Night   09     4   15

06Jan2014 Night   11     1   38

;

run;

data temp missing;

set have;

ip=input(IPRN,?? best32.);

if missing(ip) then do;ip=0;output missing;end;

  else output temp;

drop IPRN ;

run;

proc sql;

create table x as

select a.*,b.sum

  from temp as a left join (select date,sum(m) as sum from (select date,avg(ip) as m from temp group by date,shift) group by date) as b

   on a.date=b.date;

quit;

data x;

set x;

by date;

if last.date then iprn=sum;

  else iprn=0;

drop sum ip;

run;

data want;

set missing(drop=ip) x;

if missing(iprn) then iprn= 0;

run;

Xia Keshan

Contributor BSL
Contributor
Posts: 25

Re: Nested query of sum for grouped average and replacing all missing,null values

Thank you soooooooo much...xia.

You are such a great genius Smiley HappySmiley HappySmiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 399 views
  • 0 likes
  • 3 in conversation