DATA Step, Macro, Functions and more

all dates between range

Reply
Regular Contributor
Posts: 168

all dates between range

[ Edited ]

 

Hi All,

 

 I want to create variable with all the dates between the dates ranges.

 

for example

 

have:

id      orddt     recdt

100   1/5/15   2/3/15

100    1/17/15   3/6/15

......

....

101 ...............

101 ......

want;

 

1d  orddt1         recdt1       dt1                            dt2                                               orddt2                       recdt2

100 1/5/15      2/3/15    1/5/15(first orddt)

100 1/5/15      2/3/15    1/6/15

100 1/5/15      2/3/15   1/7/15

100 1/5/15      2/3/15   1/8/15

...................

......................

100 1/5/15      2/3/15  1/17/15                              1/17/15( 2nd orddt)                  1/17/15   3/6/15

100 1/5/15      2/3/15  1/18/15(upto first recdt)     1/18/15                                      1/17/15   3/6/15

........................

....................

100 1/5/15      2/3/15   2/2/15

100 1/5/15      2/3/15   2/3/15

100                                                                        2/4/15                                     1/17/15   3/6/15

100                                                                        2/5/15                                      1/17/15   3/6/15

............................

..............................

..............................

100                                                                       3/6/15(2nd recdt)                    1/17/15   3/6/15

 

my code:

 

data want;

  set have;

 by id;

array d[&max] dt1-dt&max.;

  do i=1 to &max;

     do dt=orddt    to     recdt;

       d[i]=dt;

       output;

    end;

end;

format dt-dt&max. date9.;

run;

 

i am missing some thing in my code.

 

Thanks

Sam

Super User
Posts: 17,868

Re: all dates between range

Whats the logic behind date2 variable?
Regular Contributor
Posts: 168

Re: all dates between range

Same , orddt to recdt

 

Thanks

Sam

Super User
Posts: 17,868

Re: all dates between range

That format doesn't make sense to me, in terms of future usage.

The number of overlaps/max could get unwieldy quickly.

Consider using a multistep process

1. Loop from ord to rec date and ouput one record per day per order.
2. Sort by ID, Date
3. Transpose via proc transpose. You may need to add an order var - not sure about this step.
Regular Contributor
Posts: 168

Re: all dates between range

Thanks Reeza!!! . I tried the way you suggested, but doesnt work!!! i created a order variable!! still not able to get what i want

Any suggestion will be helpful

 

Thanks

Sam

Super User
Posts: 17,868

Re: all dates between range

Post your code and define doesn't work.

Ask a Question
Discussion stats
  • 5 replies
  • 274 views
  • 0 likes
  • 2 in conversation