Sql grouping & summation

[LearnByMistk]

Hi All,

I have 

ID  currdate  pvecnt nvecnt

1  11apr2016      0           2

1 12 apr 2016     3          4

1 13apr2016      5           5

1 16apr2016      6            10

i need 2 vars sum(pve/nve)  cnt on weekend and sum(pve/nve) cnt on weekday 

logic i used 

proc sql;

 select id ,(case when pvecnt >0 and nvecnt>0 and weekday(currdate) in (1,7) then sum(pvecnt,nvecnt) as weekend_cnt,

case when pvecnt >0 and nvecnt >0 and weekday(currdate) not in (1,7) then sum(pvecnt,nvecnt) as weekday_cnt)

 from table group by id;

 quit;

rather than getting 1 result m getting 4 rows and sum of their vars in horizontally 

what i want :

 id  wknd_cnt wkdy_Cnt 

1     16               17

i'm not considering row where either of pvecnt or nvecnt is 0.

Thanks 

[RW9]

Hi,

Its a good idea to pot your test data in the form of a datastep.  Not sure I got the logic for your second point right as I get a different number:

data have;
  informat currdate date9.;
  input ID  currdate  pvecnt nvecnt;
  format currdate date9.;
datalines;
1 11apr2016      0           2
1 12apr2016     3          4
1 13apr2016      5           5
1 16apr2016      6            10
;
run;

proc sql;
  create table WANT as
  select  ID,
          sum(WE) as SUM_WE,
          sum(WD) as SUM_WD
  from    (select ID,
                  case when weekday(CURRDATE) in (1,7) then sum(PVECNT,NVECNT) else . end as WE,
                  case when weekday(CURRDATE) not in (1,7) then sum(PVECNT) else . end as WD 
           from HAVE where PVECNT > 0 and NVECNT > 0)
  group by ID;
quit;

[Kurt_Bremser]

Your SQL fails with syntax errors.

Data step solution;

data have;
infile cards;
informat
  id 1.
  currdate date9.
  pvecnt 3.
  nvecnt 3.
;
format currdate date9.;
input
  ID 
  currdate
  pvecnt
  nvecnt
;
cards;
1 11apr2016 0 2
1 12apr2016 3 4
1 13apr2016 5 5
1 16apr2016 6 10
;
run;

data want (keep=ID weekend_cnt weekday_cnt);
set have;
by ID;
retain weekend_cnt weekday_cnt;
if first.ID
then do;
  weekend_cnt = 0;
  weekday_cnt = 0;
end;
if pvecnt > 0 and nvecnt > 0
then do;
  if weekday(currdate) in (1,7)
  then weekend_cnt + sum(pvecnt,nvecnt);
  else weekday_cnt + sum(pvecnt,nvecnt);
end;
if last.ID then output;
run;

[RW9]

Sorry, did you mean my SQL Kurt, its hard to tell what response is to which post with the forum software.  Mine runs fine on my machine?

[Kurt_Bremser · Accepted solution]

---

@RW9 wrote:

Sorry, did you mean my SQL Kurt, its hard to tell what response is to which post with the forum software.  Mine runs fine on my machine?

---

Sorry, no. I meant the original post.

[LearnByMistk]

i wrote this query using alias var names and yes sql in questions isnt correct it missed end statment .

But thanks both of you for revert.