DATA Step, Macro, Functions and more

Sql grouping & summation

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Sql grouping & summation

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 


Accepted Solutions
Solution
‎05-24-2016 07:08 AM
Super User
Posts: 7,782

Re: Sql grouping & summation


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: Sql grouping & summation

Posted in reply to LearnByMistk

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;
Super User
Posts: 7,782

Re: Sql grouping & summation

Posted in reply to LearnByMistk

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,955

Re: Sql grouping & summation

Posted in reply to KurtBremser

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?

Solution
‎05-24-2016 07:08 AM
Super User
Posts: 7,782

Re: Sql grouping & summation


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: Sql grouping & summation

Posted in reply to KurtBremser

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 255 views
  • 0 likes
  • 3 in conversation