topic Re: Sql grouping & summation in SAS Programming

Sql grouping & summation

LearnByMistk — Tue, 24 May 2016 09:26:26 GMT

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

Re: Sql grouping & summation

RW9 — Tue, 24 May 2016 10:10:22 GMT

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;

Re: Sql grouping & summation

Kurt_Bremser — Tue, 24 May 2016 10:19:55 GMT

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;

Re: Sql grouping & summation

RW9 — Tue, 24 May 2016 10:41:18 GMT

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?

Re: Sql grouping & summation

Kurt_Bremser — Tue, 24 May 2016 10:51:43 GMT

@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.

Re: Sql grouping & summation

LearnByMistk — Tue, 24 May 2016 11:10:59 GMT

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.