rolling window in "proc sql"

Reply
New Contributor
Posts: 3

rolling window in "proc sql"

I want to construct a variable that captures the number of observations with the same subject over a 5 year window prior to each date. The date variable "ann_date" is in numeric format such as 19820303. I used different sas code as follows. But the results I get is the number of observations within the same group(subject), the past 5-year rolling window seems not working. Can anyone please give some suggestions on how I can get the rolling window to work? Thanks a lot!

/* the first approach I used*/

proc sort data=var;

by code;

run;

data var;

set var;

by code ;

cnt+1;

if first.code then cnt=1;

run;

proc sort data=var; by code descending cnt; run;

data var;

set var;

by code;

retain totcnt;

if first.code and ann_date-50000 <= ann_date then totcnt=cnt;run;

/* the second approach I used*/

proc sql;

create table var

as select *,count(code) as cnt

from primary.main

where ann_date -50000<= ann_date

group by code

order by code, ann_date;

quit;

/* the third approach I used*/

%let J=5;

proc sql noprint;

create table var2

as select *,count(b.code) as cnt

from var as a,

var as b

where a.code=b.code and b.ann_date1 between a.ann_date1 and intnx('year',a.ann_date1, -&J)

group by b.code

order by b.code, b.ann_date1;

quit;

Respected Advisor
Posts: 3,902

Re: rolling window in "proc sql"

Just realized that I've given a rolling sum and not a count. So this post won't answer the OT's question. I still leave the code - may be the OT has some use for it as well.

The first thing you need to make sure is that your variable "ann_date" contains a SAS date value (number of days since 1/1/1960). This will allow you to use SAS calendar functions, eg. for shifting a date 5 years back.

In case your "ann_date" variable does not contain a SAS date value below code demonstrating how you can convert it to one:

data _null_;

  ann_date=19820303;

  SAS_date=input(put(ann_date,10.),yymmdd10.);

  put "SAS_date as number:          " SAS_date;

  put "SAS_date formatted date9.:   " SAS_date date9.;

  put "SAS_date formatted yymmddn.: " SAS_date yymmddn.;

run;

Below a SQL approach calculating this roling 5year sum. It's from a processing point of view not the most efficient way of doing things - but it's easy to code.

In case you have to deal with a lot of rows and performance is important then a data step approach e.g. using a hash/iter object would most likely be more efficient - but the code would also become more complicated.

data have(keep=code date value);
  format date date9.;
  startdate=intnx('year',today(),-10,'s');
  enddate=today();
  do code=1 to 2;
    do date=startdate to enddate by 30;
      value=ceil(ranuni(1)*10);
      output;
    end;
  end;
run;


proc sql;
  create table want as 
  select
      l.*
    , sum(r.value) as sum_value_roling5years
  from have L left join have R
  on l.code=r.code and r.date>=intnx('year',l.date,-5,'s') and r.date<l.date
  group by l.code, l.date, l.value
  ;
quit;

Super Contributor
Posts: 1,636

Re: rolling window in "proc sql"

borrowed PG's code:

data have;

informat ann_date mmddyy10.;

format ann_date yymmdd10.;

input id ann_date;

cards ;

1 01/01/2001

1 01/01/2002

1 01/01/2003

1 01/01/2004

1 05/01/2005

1 06/01/2005

1 01/01/2006

1 01/01/2007

1 01/01/2008

2 01/01/2001

2 01/01/2002

2 01/01/2003

2 01/01/2004

2 01/01/2005

2 01/01/2006

2 01/01/2007

2 01/01/2008

;

proc sql;

create table periods as

select distinct

id,ann_date as period ,

intnx("year",ann_date,-5,"s") as periodBeg format=yymmdd10.,

ann_date as periodEnd format=yymmdd10.

from have;

create table want as

select a.id,period, count(period) as totcnt

from periods b inner join have a on ann_date between periodBeg and periodend and a.id=b.id

group by a.id,period;

drop table periods;

select * from want;

quit;

Linlin

Message was edited by: Linlin

Respected Advisor
Posts: 3,124

Re: rolling window in "proc sql"

Hi LinLin,

Your code seems to generate more records than it originally has. Your input data has only 17 records, while your output will have 20. It seems to me that somehow your output inter-populated between different 'id's. I can't really say whether it makes sense or not, since OP did not provide sample data.

Anyway, here is my attempt using SQL, and generate same amount of obs in output as input data.

data have;

informat ann_date mmddyy10.;

format ann_date yymmdd10.;

input id ann_date;

cards ;

1 01/01/2001

1 01/01/2002

1 01/01/2003

1 01/01/2004

1 05/01/2005

1 06/01/2005

1 01/01/2006

1 01/01/2007

1 01/01/2008

2 01/01/2001

2 01/01/2002

2 01/01/2003

2 01/01/2004

2 01/01/2005

2 01/01/2006

2 01/01/2007

2 01/01/2008

;

proc sql;

create table want as

select a.id, ann_date, count(_ann_date) as _ct from have a, have (rename=ann_date=_ann_date) b

where a.id=b.id and intnx('year',ann_date,-5,'s') le _ann_date le ann_date

group by a.id ,ann_date

order by a.id, ann_date;

quit;

proc print;run;

Regards,

Haikuo

Respected Advisor
Posts: 4,659

Re: rolling window in "proc sql"

If I may, since I was kindly quoted by . The "with the same subject" part of OP's request is missing. The query should read:

proc sql;

create table periods as

select distinct

id, ann_date as period ,

intnx("year",ann_date,-5,"s") as periodBeg format=yymmdd10.,

ann_date as periodEnd format=yymmdd10.

from have;

create table want as

select periods.id, period, count(*) as totcnt

from periods inner join have on periods.id=have.id and ann_date between periodBeg and periodend

group by periods.id, period;

drop table periods;

select * from want;

quit;

PG

PG
Super Contributor
Posts: 1,636

Re: rolling window in "proc sql"

Hi Haikuo,

Thank you! I have made changes to my code.

Linlin

Super Contributor
Posts: 1,636

Re: rolling window in "proc sql"

Hi PG and Haikuo,

How are you doing? I miss you guys! Do you use JMP?

Thanks - Linlin

Respected Advisor
Posts: 4,659

Re: rolling window in "proc sql"

Hi Linlin,

Yes, I use JMP. I find it quite powerful and quick for exploratory data analysis. For example, I love the missing data pattern and partition models platforms. Moreover, I recommend JMP to my non programming colleagues and I would be uncomfortable recommending something I don't use! I stay away from any kind of programming in JSL, I find it awkward; I move to SAS for any analysis that requires programming.

Cheers!

PG

PG
Super Contributor
Posts: 1,636

Re: rolling window in "proc sql"

what is JSL?

Respected Advisor
Posts: 4,659

Re: rolling window in "proc sql"

JMP Scripting Language. JMP is fun for its interactivity. But when I got into JSL, I found it a pain. But like anything else, it is mostly a matter of taste. - PG

PG
Respected Advisor
Posts: 3,124

Re: rolling window in "proc sql"

PG and LinLin,

It is 7:50pm central time on Sunday, and I thought I was the only one who does not have a life. LOL.

Haikuo

Respected Advisor
Posts: 4,659

Re: rolling window in "proc sql"

Gives me something to do between the dances :smileysilly:!

PG
Super Contributor
Posts: 1,636

Re: rolling window in "proc sql"

HaiKuo,

It is an important part of my life to meet your guys on the forum. Too bad Art doesn't have time with us anymore.

Cheers - Linlin

Respected Advisor
Posts: 4,659

Re: rolling window in "proc sql"

I miss Art also, especially his diplomacy and patience... - PG

PG
Respected Advisor
Posts: 3,124

Re: rolling window in "proc sql"

Last time I saw Art's post on SAS-L was 6/27.

Haikuo

Ask a Question
Discussion stats
  • 20 replies
  • 1516 views
  • 6 likes
  • 6 in conversation