Hi,
I've a datadata consisting of daily data from 0 day to 7300 day (20 years of data); so far there is no day gap between records, so the days are consecutive. I'd like to output the data for every 30 days (30th, 60th) and ideally after 360 days, I'd like to jump to 366 and start again for every 30 days until the next 360th days and jump to 731, and so on. How do I do it? If it is too complicate, I can do every 30 days (plus the first record). The data would look something like the following:
data aa; input
day amount;
datalines;
0 12 -> output
1 23
2 50
30 34 -> output
31 34
.
..
60 41 -> output
....
360 3 -> output
365 3
366 1
367 4
....
;
Thanks in advance for your help.
data _null_;
input day amount;
if mod(day,30)=0 then put (day amount) (/=);
cards;
0 12
1 23
2 50
30 34
31 34
60 41
360 3
365 3
366 1
367 4
;
run;
day=0
amount=12
day=30
amount=34
day=60
amount=41
day=360
amount=3
Thanks a lot. It is a really neat way to do it. I thought about something like that (use division day/30) but didn't know how to proceed after that. I also just found the following way and it seemed to work (although it doesn't address the 365 days problem.)
data aa2 set aa;
do i = 0 to 7300 by 30;
if day=i then output;
end;
drop i;
run;
Hi Solph,
Is this what you want?
data have;
do day=0 to 7300;
output;
end;
run;
data want(drop=i j);
set have;
if day=0 then output;
do j=0 to 19;
do i=j*365 to j*365+360 by 30;
if day=i and mod(day,365)ne 0 then output;
end;
end;
run;
Thanks Linlin. I just saw your reply and tested it. Yes it is the alternative approach I was thinking about. I'll just need to understand the logic of the code. Thanks so much.
Do you have dates in the file? Otherwise, the 365 seems arbitray/biased, as it doesn't account for leap years.
Not in this file, although I could create a pseudo date variable if it helps. Unfortunately we'd have to to output for every 30 days (has to be integer), although ultimately we are reporting the data by year. It is going to be difficult to reconcile the days differences one way or another. Another way is program by month of course, if so I guess a date variable would be useful, right?
Do you need equal intervals or can you take the last day of every month?
I think you need to know the date. Otherwise you don’t know if ‘366’ is last day of a leap year or first day of next year. Other solution could be to have these day numbers starting from 1 every new year:
363
364
365
1
2
3
4
..
Even without the presence of an explicit date variable, it seems possible to use the INTNX function to avoid drift caused by leap years.
data have ;
do Day= 0 to 7300 ;
output ;
end ;
run;
data want (keep = day) ;
counter = 0 ;
do outer = 0 to 19 ;
anniversary = intnx('year', 0, outer, 'same') ;
do inner = 0 to ifn(outer LT 19, 330, 360) by 30 ;
next = anniversary + inner ;
do counter = counter to next ;
set have ;
end ;
output ;
end ;
end ;
run ;
art297 wrote:
Do you have dates in the file? Otherwise, the 365 seems arbitray/biased, as it doesn't account for leap years.
data have; do day=0 to 7300; output; end; run; data want; set have; if _n_ eq 1 then do;output;return;end; count+1; if mod(group,12)=0 and count=31 then do;count=-4;end; mod=mod(count,30); if mod=1 then do;group+1; count=1;end; if mod=0 and count ne 0 then output; run;
Ksharp
sometimes refered to as a DOW loop, because it loops around a SET statement (but iirc named after presenters who first brought that idea to prominence Paul Dorfman and Ian Whitlock),
this step might do what was wanted = re-start counting after 365, 730,
I tested on the first 1200 rows of sashelp.prdsale >>>>
data thirtys ;
do row = 1 to 365 ;
set sashelp.prdsale( obs=1200) ;
read+1 ;
if not mod( row, 30 ) then output ;
end ;
put read= ;
run
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.