Hi Guys,
I would need your help on the problem that I'm currently into...
Basically I have the data below.
I would like to extract the data having criteria:
Please note:
Thank you in advance
Data Have
:
row | Group | dollar | time |
1 | a | 10 | 2:43 AM |
2 | a | 10 | 8:34 AM |
3 | a | 11 | 11:52 AM |
4 | b | 16 | 5:32 AM |
5 | b | 18 | 7:16 AM |
6 | b | 12 | 2:08 PM |
7 | b | 11 | 8:34 PM |
8 | b | 13 | 9:31 PM |
9 | c | 11 | 2:18 AM |
10 | c | 5 | 2:19 AM |
11 | c | 5 | 2:20 AM |
12 | c | 12 | 8:34 AM |
13 | d | 18 | 2:12 PM |
14 | e | 19 | 2:43 AM |
15 | e | 17 | 5:30 AM |
16 | e | 11 | 12:07 PM |
17 | e | 14 | 8:40 PM |
18 | e | 16 | 8:46 PM |
19 | f | 11 | 8:24 AM |
20 | f | 12 | 11:55 AM |
Data Wants:
row | Group | dollar | time | delta time | Remarks |
1 | a | 10 | 2:43 AM | ||
2 | a | 10 | 8:34 AM | 5:51 | |
3 | a | 11 | 11:52 AM | 3:18 | |
4 | b | 16 | 5:32 AM | Yes | |
5 | b | 18 | 7:16 AM | 1:43 | Yes |
6 | b | 12 | 2:08 PM | 6:51 | |
7 | b | 11 | 8:34 PM | 6:26 | |
8 | b | 13 | 9:31 PM | 0:57 | |
9 | c | 11 | 2:18 AM | Yes | |
10 | c | 5 | 2:19 AM | 0:01 | Yes |
11 | c | 5 | 2:20 AM | 0:01 | Yes |
12 | c | 12 | 8:34 AM | 6:14 | |
13 | d | 18 | 2:12 PM | 5:38 | |
14 | e | 19 | 2:43 AM | Yes | |
15 | e | 17 | 5:30 AM | 2:47 | Yes |
16 | e | 11 | 12:07 PM | 6:37 | |
17 | e | 14 | 8:40 PM | 8:32 | Yes |
18 | e | 16 | 8:46 PM | 0:05 | Yes |
19 | f | 11 | 8:24 AM | ||
20 | f | 12 | 11:55 AM | 3:31 |
Message was edited by: feira h --> to provide better explanation.
So you want all of them ?
time dif
...............
08:00 04:20 YES
09:00 01:00 YES
11:30 02:30 YES
data have; input Group $ dollar time24hrs : time.; format time24hrs time.; cards; a 10 2:43 a 10 8:34 a 11 11:52 b 16 5:32 b 18 7:16 b 12 14:08 b 11 20:34 b 13 21:31 c 11 2:18 c 12 8:34 d 18 14:12 e 19 2:43 e 17 5:30 e 11 12:07 e 14 20:40 e 16 20:46 f 11 8:24 f 12 11:55 ; run; proc sql; create table want as select *,case when (select count(*) from have where Group=a.Group and time24hrs between a.time24hrs-3*60*60 and a.time24hrs+3*60*60) ge 2 or (select sum(dollar) from have where Group=a.Group and time24hrs between a.time24hrs-3*60*60 and a.time24hrs+3*60*60) ge 25 then 'YES' else 'NO ' end as falg from have as a; quit;
Xia Keshan
You didn't post the output you want yet .
data have; input Group $ dollar time24hrs : time.; format time24hrs time.; cards; a 10 2:43 a 10 8:34 a 11 11:52 b 16 5:32 b 18 7:16 b 12 14:08 b 11 20:34 b 13 21:31 c 11 2:18 c 12 8:34 d 18 14:12 e 19 2:43 e 17 5:30 e 11 12:07 e 14 20:40 e 16 20:46 f 11 8:24 f 12 11:55 ; run; proc sql; create table want as select * from have group by group having range(time24hrs) le 30*60 and (count(*) ge 3 or sum(dollar) ge 25); quit;
Xia Keshan
You didn't post the output you want yet .
data have; input Group $ dollar time24hrs : time.; format time24hrs time.; cards; a 10 2:43 a 10 8:34 a 11 11:52 b 16 5:32 b 18 7:16 b 12 14:08 b 11 20:34 b 13 21:31 c 11 2:18 c 12 8:34 d 18 14:12 e 19 2:43 e 17 5:30 e 11 12:07 e 14 20:40 e 16 20:46 f 11 8:24 f 12 11:55 ; run; proc sql; create table want as select * from have group by group having range(time24hrs) le 30*60 and (count(*) ge 3 or sum(dollar) ge 25); quit;
Xia Keshan
It is more complicated than I imaged . What you gonna do if you have data :
time dif
...............
08:00 04:20
09:00 01:00
11:30 02:30
You want
08:00 04:20 YES
09:00 01:00 YES
or want ?
09:00 01:00 YES
11:30 02:30 YES
Hi Xia,
Assuming the criteria of total dollar >=25 are met, then the output should be all of them, because:
Thanks for your assistance...
So you want all of them ?
time dif
...............
08:00 04:20 YES
09:00 01:00 YES
11:30 02:30 YES
data have; input Group $ dollar time24hrs : time.; format time24hrs time.; cards; a 10 2:43 a 10 8:34 a 11 11:52 b 16 5:32 b 18 7:16 b 12 14:08 b 11 20:34 b 13 21:31 c 11 2:18 c 12 8:34 d 18 14:12 e 19 2:43 e 17 5:30 e 11 12:07 e 14 20:40 e 16 20:46 f 11 8:24 f 12 11:55 ; run; proc sql; create table want as select *,case when (select count(*) from have where Group=a.Group and time24hrs between a.time24hrs-3*60*60 and a.time24hrs+3*60*60) ge 2 or (select sum(dollar) from have where Group=a.Group and time24hrs between a.time24hrs-3*60*60 and a.time24hrs+3*60*60) ge 25 then 'YES' else 'NO ' end as falg from have as a; quit;
Xia Keshan
Thanks a lot Xia... it's very helpful... !!!
Please mark the question as answered and award the correct answers to extraordinary master Xia Keshan.
I tried to play around with your requirement, but unfortunately couldn;t meet it properly. But for learning purpose, you can play and have fun
data have;
input Group $ dollar time24hrs : time.;
*format time24hrs time.;
cards;
a 10 2:43
a 10 8:34
a 11 11:52
b 16 5:32
b 18 7:16
b 12 14:08
b 11 20:34
b 13 21:31
c 11 2:18
c 12 8:34
d 18 14:12
e 19 2:43
e 17 5:30
e 11 12:07
e 14 20:40
e 16 20:46
f 11 8:24
f 12 11:55
;
run;
data want new(keep=group remarks);
set have;
by group;
k=lag(time24hrs);
if first.group then
do;
totaldollar=0;
grpcount=0;
sumtimediff=0;
end;
grpcount+1;
totaldollar+dollar;
if not first.group then
do;
deltatime=intck('hour',k,time24hrs);
sumtimediff+deltatime;
end;
if grpcount>=2 and totaldollar>=25 and sumtimediff le 3 then
do;
remarks='yes';
output new;
end;
output want;
run;
data final;
merge want new;
by group;
keep group dollar time24hrs remarks;
run;
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.