CODEWITHSUNDEEP
databasesas
Kelly
Kelly

Reputation: 145

Error in do loop, creating variables over a time span in SAS

I have a dataset that includes start and end dates for a given project. I am trying to count up how many projects we are running per year and so if a project goes from 2010-2013 then it should be counted in 2010, 2011, 2012, and 2013. I had this working in a prior dataset but running it on a new dataset I m getting an error in the do loop.

|Project |StartDate| EnDDate | UserID |
|:-------:|:--------:|:--------:|:-------:|
|Proj1   |1/15/2010|3/21/2013| 1|
|Proj2   |7/31/2015| 9/3/2018|  2|

Here's what I am looking to get:

|Project |StartDate |EnDDate   |Year |UserID|
|:-------:|:---------:|:---------:|:----:|:-----:|
|   Proj1 |  1/15/2010| 3/21/2013 |2010 |1|
|   Proj1  | 1/15/2010| 3/21/2013 |2011 |1|
|   Proj1  | 1/15/2010| 3/21/2013 |2012 |1|
|   Proj1  | 1/15/2010| 3/21/2013 |2013 |1|
   ...

I have been using the following code

data test2;
 set users;
 do i = 0 to (year(enddate)-year(startdate));
  year = year(startdate)+i;
  drop i;
  output;
 end;
run;

but I get the following error: ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.

I have tried sorting by the startdate, enddate, startdate and enddate, and changing the date format to no avail (it's DATE9.). I must be missing something obvious before because the code worked with a prior databse but I'm not sure what I could be missing.

Any help is greatly appreciated.

Upvotes: 0

Views: 185

Answers (1)

Tom
Tom

Reputation: 51566

Make sure you have non-missing start and end dates before trying to use the values to control a DO loop.

data test2;
  set users;
  if 2=n(startdate,enddate) then do year = year(startdate) to year(enddate);
    output;
  end;
run;

Upvotes: 3

Related Questions