topic Re: how to loop through dates within a by group in SAS Programming

how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 14:55:09 GMT

I have got a dataset as below and I need to loop through the dates increaed by 1 in each by group of ID

data have;
format eff_dat date9. ;
infile cards dlm = ',' ;
input eff_dat date9. id amt;
cards;
30dec2008,1221,20
01feb2009,1221,30
01mar2009,1222,10
01apr2009,1222,15
;run;

I need the result as below

30-Dec-08	1221	20
31-Dec-08	1221	20
01-Jan-09	1221	20
02-Jan-09	1221	20
03-Jan-09	1221	20
04-Jan-09	1221	20
05-Jan-09	1221	20
06-Jan-09	1221	20
07-Jan-09	1221	20
08-Jan-09	1221	20
09-Jan-09	1221	20
10-Jan-09	1221	20
11-Jan-09	1221	20
12-Jan-09	1221	20
13-Jan-09	1221	20
14-Jan-09	1221	20
15-Jan-09	1221	20
16-Jan-09	1221	20
17-Jan-09	1221	20
18-Jan-09	1221	20
19-Jan-09	1221	20
20-Jan-09	1221	20
21-Jan-09	1221	20
22-Jan-09	1221	20
23-Jan-09	1221	20
24-Jan-09	1221	20
25-Jan-09	1221	20
26-Jan-09	1221	20
27-Jan-09	1221	20
28-Jan-09	1221	20
29-Jan-09	1221	20
30-Jan-09	1221	20
31-Jan-09	1221	20
01-Feb-09	1221	30
02-Feb-09	1221	30
03-Feb-09	1221	30
04-Feb-09	1221	30
05-Feb-09	1221	30
06-Feb-09	1221	30
07-Feb-09	1221	30
08-Feb-09	1221	30
09-Feb-09	1221	30
10-Feb-09	1221	30
11-Feb-09	1221	30
12-Feb-09	1221	30
13-Feb-09	1221	30
14-Feb-09	1221	30
15-Feb-09	1221	30
16-Feb-09	1221	30
17-Feb-09	1221	30
18-Feb-09	1221	30
19-Feb-09	1221	30
20-Feb-09	1221	30
21-Feb-09	1221	30
22-Feb-09	1221	30
23-Feb-09	1221	30
24-Feb-09	1221	30
25-Feb-09	1221	30
26-Feb-09	1221	30
27-Feb-09	1221	30
28-Feb-09	1221	30
01-Mar-09	1222	10
02-Mar-09	1222	10
03-Mar-09	1222	10
04-Mar-09	1222	10
05-Mar-09	1222	10
06-Mar-09	1222	10
07-Mar-09	1222	10
08-Mar-09	1222	10
09-Mar-09	1222	10
10-Mar-09	1222	10
11-Mar-09	1222	10
12-Mar-09	1222	10
13-Mar-09	1222	10
14-Mar-09	1222	10
15-Mar-09	1222	10
16-Mar-09	1222	10
17-Mar-09	1222	10
18-Mar-09	1222	10
19-Mar-09	1222	10
20-Mar-09	1222	10
21-Mar-09	1222	10
22-Mar-09	1222	10
23-Mar-09	1222	10
24-Mar-09	1222	10
25-Mar-09	1222	10
26-Mar-09	1222	10
27-Mar-09	1222	10
28-Mar-09	1222	10
29-Mar-09	1222	10
30-Mar-09	1222	10
31-Mar-09	1222	10

Re: how to loop through dates within a by group

ballardw — Mon, 15 Apr 2019 15:07:27 GMT

I don't see a BY group anywhere.

Is your actual requirement to create additional records looking ahead such as from 30Dec to one day before the start of the date on the next record?

Re: how to loop through dates within a by group

PeterClemmensen — Mon, 15 Apr 2019 15:38:26 GMT

Do someting like this

data have;
format eff_dat date9. ;
infile cards dlm = ',' ;
input eff_dat date9. id amt;
cards;
30dec2008,1221,20
01feb2009,1221,30
01mar2009,1222,10
01apr2009,1222,15
;run;

data want(keep=date id amt);
   merge have end=lr
         have(firstobs=2 keep=eff_dat rename = (eff_dat=lead_dat));

   if not (lr) then do date=eff_dat to lead_dat-1;
      output;
   end;

   format date date9.;
run;

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:02:43 GMT

Thanks for the reply Ballardw.

but it seemlike it is not switching on to the next BY group so the result is stopping at the 1st BY grup (1221)

1221 30 18FEB2009
1221 30 19FEB2009
1221 30 20FEB2009
1221 30 21FEB2009
1221 30 22FEB2009
1221 30 23FEB2009
1221 30 24FEB2009
1221 30 25FEB2009
1221 30 26FEB2009
1221 30 27FEB2009
1221 30 28FEB2009

Nothing after the above

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:04:35 GMT

Apologies Draycut.

The above reply ment for you.

Re: how to loop through dates within a by group

yabwon — Mon, 15 Apr 2019 16:06:14 GMT

Hi,

maybe try something like this:

data have;
format eff_dat date9. ;
infile cards dlm = ',' ;
input eff_dat date9. id amt;
cards;
30dec2008,1221,20
01feb2009,1221,30
01mar2009,1222,10
01apr2009,1222,15
;
run;


data want;
  loop =  eff_dat; drop loop tmp;
  put _all_;

  set have;

  tmp = eff_dat;
  if loop ne . then
    do _N_ = loop to eff_dat-1;
      eff_dat = _N_;
      output;
    end;
  eff_dat = tmp;

run;

all the best

Bart

Re: how to loop through dates within a by group

PeterClemmensen — Mon, 15 Apr 2019 16:07:27 GMT

No problem 🙂

Did you run my exact code? When I run it, I get 92 observations. The exact 92 observations you post in your question...

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:14:16 GMT

Hi Yabwon

I should get amt as 20 for the below

30DEC2008 1221 30
31DEC2008 1221 30
01JAN2009 1221 30
02JAN2009 1221 30
03JAN2009 1221 30
04JAN2009 1221 30
05JAN2009 1221 30
06JAN2009 1221 30
07JAN2009 1221 30
08JAN2009 1221 30
09JAN2009 1221 30
10JAN2009 1221 30
11JAN2009 1221 30

as you can see from the program below from 30dec2008 until 31jan2009 amt is 20

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:19:35 GMT

Hi Draycut,

This is what I am saying that loop is stopping on first BY varibale and not going any futher.

We needs to get the other dates as below for other BY group as well.

so for 01mar2009 it should show 1222 10 that row is missng

Re: how to loop through dates within a by group

yabwon — Mon, 15 Apr 2019 16:20:06 GMT

Hi,

Right, my bad, sorry!

Bart

try this one:

data have;
format eff_dat date9. ;
infile cards dlm = ',' ;
input eff_dat date9. id amt;
cards;
30dec2008,1221,20
01feb2009,1221,30
01mar2009,1222,10
01apr2009,1222,15
;
run;


data want;
  loop =  eff_dat; drop loop;
  put _all_;

  set have(keep=eff_dat);

  if loop ne . then
    do _N_ = loop to eff_dat-1;
      eff_dat = _N_;
      output;
    end;

  set have;

run;

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:24:12 GMT

HI Yabwon ,

That's awsome but I am missing the last record so for exampl if I have got only these three

30dec2008,1221,20
01feb2009,1221,30
01mar2009,1222,10

then I am not geting 01mar2009,1222,10 in my result

Re: how to loop through dates within a by group

PeterClemmensen — Mon, 15 Apr 2019 16:33:55 GMT

No. It is not missing. It is there, exactly as you desire

Re: how to loop through dates within a by group

yabwon — Mon, 15 Apr 2019 16:34:45 GMT

ok, I think that one will fix the issue(I narrowed data in the example, to make it easier to test)

Bart

data have;
format eff_dat date9. ;
infile cards dlm = ',' ;
input eff_dat date9. id amt;
cards;
30dec2008,1221,20
05jan2009,1222,30
10jan2009,1223,10
15jan2009,1224,15
;
run;


data want;
  loop =  eff_dat; drop loop;
  put _all_;

  set have(keep=eff_dat);

  if loop ne . then
    do _N_ = loop to eff_dat-1;
      eff_dat = _N_;
      output;
    end;

  set have end=eof;

  if eof then output; 

run;

Re: how to loop through dates within a by group

MV04 — Mon, 15 Apr 2019 16:41:04 GMT

Thanks so much Yabwon .

That did work.