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 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.
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;
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;
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?
@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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.