- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi. I have a dataset I'm reading in for testing purposes. I hope you can help me with this problem. It has three variables ID, FROM_DT, and CODE.
For each ID I need to be able to count the number of records for a given increment of time using the FROM_DT variable (so by Week or Month for example) from between my start date (&BGN_DT) and my end date (&END_DT). A requirement is that I need to use Proc SQL for this.
Using PROC SQL, is there a way to Group By ID and count the number of records by a particular time increment. Again, like for Week (Sunday to Saturday for Weeks 1 thru 52) and/or for Month (Jan to Dec)?
Any help here would be greatly appreciated!
%LET BGN_DT='01JAN2019'D;
%LET BGN_DT='31JAN2019'D;
DATA HAVE;
INPUT ID $ FROM_DT : DATE9. CODE $;
FORMAT FROM_DT DATE9.;
DATALINES;
F23 01JAN2019 T234
F23 30JAN2019 1234
F23 21FEB2019 1234
F23 25FEB2019 Z234
F23 23FEB2019 X234
F23 18MAR2019 1234
F23 16MAR2019 1234
F23 21MAR2019 1234
F23 31MAR2019 1234
F23 21APR2019 1234
F23 17APR2019 1234
F23 29APR2019 1234
F23 13MAY2019 1234
F23 19MAY2019 1234
F23 04MAY2019 1234
F23 01JUN2019 T234
F23 30JUN2019 1234
F23 21AUG2019 1234
F23 25AUG2019 Z234
F23 23AUG2019 X234
F23 18SEP2019 1234
F23 16SEP2019 1234
F23 21SEP2019 1234
F23 30SEP2019 1234
F23 21NOV2019 1234
F23 17NOV2019 1234
F23 29NOV2019 1234
F23 13DEC2019 1234
F23 19DEC2019 1234
F23 04DEC2019 1234
S34 01JAN2019 A344
S34 30JAN2019 A344
S34 21FEB2019 2344
S34 25FEB2019 E344
S34 23FEB2019 2344
S34 18MAR2019 2344
S34 16MAR2019 B344
S34 21MAR2019 B344
S34 31MAR2019 B344
S34 21APR2019 2344
S34 17APR2019 2344
S34 29APR2019 C344
S34 13MAY2019 D344
S34 19MAY2019 2344
S34 04MAY2019 2344
S34 01JUN2019 A344
S34 30JUN2019 A344
S34 21JUL2019 2344
S34 25JUL2019 E344
S34 23JUL2019 2344
S34 18AUG2019 2344
S34 16AUG2019 B344
S34 21AUG2019 B344
S34 30AUG2019 B344
S34 21NOV2019 2344
S34 17NOV2019 2344
S34 29NOV2019 C344
S34 13DEC2019 D344
S34 19DEC2019 2344
S34 04DEC2019 2344
;
RUN;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try
proc sql;
select
id,
intnx("week", from_dt, 0) as week "Week starting on" format=yymmdd10.,
count(*) as count
from have
where from_dt between &bgn_dt. and &end_dt.
group by id, calculated week;
quit;
replace week by month for monthly counts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You could use the INTNX function in PROC SQL to count the number of weeks/months since &BGN_DT.
It's also very easy to do in PROC FREQ or PROC SUMMARY
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi. Any idea what the INTNX function syntax would look like for my example in Proc Sql? Say for the Week example. I need to somehow use both INTNX and the COUNT function I think because in the end I need a dataset with three variables; ID, WEEK_NUM (or MONTH_NAME), and the actual Count.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I should have said the INTCK function. Plenty of examples here:
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
>A requirement is that I need to use Proc SQL for this.
This seems like a silly requirement
> is there a way to Group By ID and count the number of records
Like this?
proc sql;
select ID, sum( FROM_DT between &BGN_DT and &END_DT) as count
from HAVE
group by ID ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@ChrisNZ wrote:
>A requirement is that I need to use Proc SQL for this.
This seems like a silly requirement
Agreed.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try
proc sql;
select
id,
intnx("week", from_dt, 0) as week "Week starting on" format=yymmdd10.,
count(*) as count
from have
where from_dt between &bgn_dt. and &end_dt.
group by id, calculated week;
quit;
replace week by month for monthly counts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, there is. In fact, with SQL it's simpler than with other tools. For example:
data have ;
input id :$3. from_dt :date. ;
format from_dt yymmdd10. ;
cards ;
f23 01jan2019
f23 30jan2019
f23 21feb2019
f23 25feb2019
f23 23feb2019
f23 18mar2019
f23 16mar2019
f23 21mar2019
f23 31mar2019
f23 21apr2019
f23 17apr2019
f23 29apr2019
f23 13may2019
f23 19may2019
f23 04may2019
f23 01jun2019
f23 30jun2019
f23 21aug2019
f23 25aug2019
f23 23aug2019
f23 18sep2019
f23 16sep2019
f23 21sep2019
f23 30sep2019
f23 21nov2019
f23 17nov2019
f23 29nov2019
f23 13dec2019
f23 19dec2019
f23 04dec2019
s34 01jan2019
s34 30jan2019
s34 21feb2019
s34 25feb2019
s34 23feb2019
s34 18mar2019
s34 16mar2019
s34 21mar2019
s34 31mar2019
s34 21apr2019
s34 17apr2019
s34 29apr2019
s34 13may2019
s34 19may2019
s34 04may2019
s34 01jun2019
s34 30jun2019
s34 21jul2019
s34 25jul2019
s34 23jul2019
s34 18aug2019
s34 16aug2019
s34 21aug2019
s34 30aug2019
s34 21nov2019
s34 17nov2019
s34 29nov2019
s34 13dec2019
s34 19dec2019
s34 04dec2019
;
run ;
%let bgn_dt='01jan2019'd ;
%let dt_int = week ;
proc sql ;
create table want as
select id
, 1 + intck ("&dt_int", &bgn_dt, from_dt) as &dt_int._number
, count (*) as count
from have
group id, &dt_int._number
;
quit ;
If you want a different date interval, for example, MONTH, just plug it into the macro parameter DT_INT instead of WEEK. Ditto for any other legitimate SAS date interval, such as QUARTER, SEMIYEAR, etc. Try then to see the effect.
Kind regards
Paul D.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content