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: 6,982

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,432

Re: Sql grouping & summation

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: 6,982

Re: Sql grouping & summation

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,432

Re: Sql grouping & summation

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: 6,982

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

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
  • 253 views
  • 0 likes
  • 3 in conversation