Create a monthly end date between year in a macro

[Solph]

Hi,

I want to run a DO LOOP in a macro where my data run is based on a point in time date at end of month each month say between 2018 and 2020 (so I'd run 12mo * 3 yrs=36 times). That is is the person is present at end of month each month based on the start date and end date of the data.

All I could come up with is such as the following. And apparently I wasn't able to apply end of month, so I just conveniently settled with first day of each month (that is if end of month isn't possible, I'm OK with first date of each month).  Much appreciated for any help you can give me.

%macro get;
%do year=2018 %to 2020;
%do month=1 %to 12;
%let PIT=%eval(mdy(month,1,&year));
proc sql; create table want&PIT as
  SELECT var1, var2 
  FROM have
  where (start <= "&PIT"d and start is not NULL) AND (END> "&PIT"d OR END IS NULL);
quit;
%end;
%end;
%mend;
%get;

This is the sample data.

data have; input ID @3 start date9. @13 end date9.;
	format start end yymmdd10.;
datalines;
1 27JAN2018	06MAY2019 
2 05MAR2019	09MAR2020
3 31OCT2017	03NOV2019
4 15JAN2018	19JAN2020
5 04JUL2015	12JUL2020
;
run;

BTW this code below would give me end of month but I'm running each date between two dates so I couldn't really apply (not to mention it won't work in a macro environment.

data dummy; 
	do date='01jan2018'd to '31dec2020'd;
	Month_EndDate=put(intnx('month',date,0,'E'), yymmdd10.);
	output;
	end;
run;
proc sort data=dummy (drop=date) nodupkey; by Month_EndDate; run;

[ballardw]

How about showing explicit values that you do want to use for an end comparison?

Since MDY is used to make &PIt returns a date value you do not want to use "&pit"d to compare a value.

Have you run your macro with the option MPRINT set to see what your code is generating?

[Solph]

Error messages in the log are

ERROR: Required operator not found in expression: mdy(month,1,2018) 
ERROR: The macro GET will stop executing.

The HAVE data should be such as the following for three 3 dates Jan 31, 2028, Dec 31, 2019 and June 30, 2020

For PIT=01JAN2018, 2 cases met the criteria present on Jan 1, 2018
   3 31OCT2017	03NOV2019
   5 04JUL2015	12JUL2020
For PIT=01DEC2019, 3 cases met the criteria present on Dec 1, 2019
   2 05MAR2019	09MAR2020
   4 15JAN2018	19JAN2020
   5 04JUL2015	12JUL2020
For PIT=01JUN2020, 1 case met the criteria present on June 1, 2020
   5 04JUL2015	12JUL2020

[ballardw]

Use

%do month=1 %to 12;
%let PIT=%sysfunc(mdy(&month,1,&year));

You need to use %sysfunc to call a data step function, not %eval. You also need to make sure you have the & to reference the macro variable.

I am still not able to follow the logic of what value you want to use for for an upper bound for a given starting PIT value.

[Solph]

Thanks for the correction. I modified the code and it worked. As seen in the final output I'm trying to count number of cases present on the point in time date each month from start year to end year. I think it can be more neat (eg the PIT is date formatted as an input value) or more flexible (e.g. allowing to chose end of month as supposedly I should count cases from start of month to end of month, not on the 1st or last date of a month e.g. the 31th). But it does the job so I can get going, so it's much appreciated. 

data have; input ID @3 start date9. @13 end date9.;
	format start end yymmdd10.;
datalines;
1 27JAN2018	06MAY2019 
2 05MAR2019	09MAR2020
3 31OCT2017	03NOV2019
4 15JAN2018	19JAN2020
5 04JUL2015	12JUL2020
;
run;
%macro get;
%do year=2018 %to 2020;
%do month=1 %to 12;
%let PIT=%sysfunc(mdy(&month,1,&year)); /*point in time date at start of each month*/
proc sql; create table want&year&month as
  SELECT id, put(&pit, yymmdd10.) as PIT, start, end 
  FROM have
  where (start <= &PIT and start is not NULL) AND (END> &PIT OR END IS NULL);
quit;
%end;
%end;
%mend;
%get;
data comb; set want2018: want2019: want2020:; run;
*Count # of cases for each PIT date;
proc sql; create table prevalence_cases as select PIT, count(*) as Prevalence_cases from comb group by 1; quit;
*Create dummy date for each month;
data dummy; 
	do date='01jan2018'd to '31dec2020'd;
	*PIT=put(intnx('month',date,0,'E'), yymmdd10.); /*end of month*/
	PIT=put(intnx('month',date,0), yymmdd10.); /*start of month*/
	output;
	end;
run;
proc sort data=dummy (drop=date) nodupkey; by PIT; run;
data final; merge dummy (in=a) prevalence_cases (in=b); by PIT; 
if a and not b then Prevalence_cases=0;
run;
proc print noobs; run;

Output is as the following. 

PIT	Prevalence_cases
1/1/2018	2
2/1/2018	4
3/1/2018	4
4/1/2018	4
5/1/2018	4
6/1/2018	4
7/1/2018	4
8/1/2018	4
9/1/2018	4
10/1/2018	4
11/1/2018	4
12/1/2018	4
1/1/2019	4
2/1/2019	4
3/1/2019	4
4/1/2019	5
5/1/2019	5
6/1/2019	4
7/1/2019	4
8/1/2019	4
9/1/2019	4
10/1/2019	4
11/1/2019	4
12/1/2019	3
1/1/2020	3
2/1/2020	2
3/1/2020	2
4/1/2020	1
5/1/2020	1
6/1/2020	1
7/1/2020	1
8/1/2020	0
9/1/2020	0
10/1/2020	0
11/1/2020	0
12/1/2020	0

[Tom]

You are looping in the wrong place. For each observations update the count for the months it contains.

Something like:

data have; 
  input ID start :date. end :date.;
  format start end yymmdd10.;
datalines;
1 27JAN2018	06MAY2019 
2 05MAR2019	09MAR2020
3 31OCT2017	03NOV2019
4 15JAN2018	19JAN2020
5 04JUL2015	12JUL2020
;

%let start="01JAN2018"d;
%let end="31DEC2020"d;
%let nmonths=%sysfunc(intck(month,&start,&end));
data count;
  array months[0:&nmonths] _temporary_ (0 &nmonths*0) ;
  set have end=eof;
  do month=intck('month',&start,max(&start,start)) to intck('month',&start,min(end,&end));
    months[month]+1;
  end;
  if eof then do month=0 to &nmonths;
    PIT=put(intnx('month',&start,month),yymm7.);
    count=months[month];
    output;
  end;
  keep pit count;
run;

Results:

Obs      PIT      count

  1    2018M01      4
  2    2018M02      4
  3    2018M03      4
  4    2018M04      4
  5    2018M05      4
  6    2018M06      4
  7    2018M07      4
  8    2018M08      4
  9    2018M09      4
 10    2018M10      4
 11    2018M11      4
 12    2018M12      4
 13    2019M01      4
 14    2019M02      4
 15    2019M03      5
 16    2019M04      5
 17    2019M05      5
 18    2019M06      4
 19    2019M07      4
 20    2019M08      4
 21    2019M09      4
 22    2019M10      4
 23    2019M11      4
 24    2019M12      3
 25    2020M01      3
 26    2020M02      2
 27    2020M03      2
 28    2020M04      1
 29    2020M05      1
 30    2020M06      1
 31    2020M07      1
 32    2020M08      0
 33    2020M09      0
 34    2020M10      0
 35    2020M11      0
 36    2020M12      0

[Solph]

Thanks Tom. I ran your code but it is returning  with value 2 for all years and months. But if I change your data input lines to the original code as below, then it's fine. Not sure why or if it matters. Your code indeed is doing the job of being present any time in a given month based on individual's start and end dates. Thanks again.

data have; input ID @3 start date9. @13 end date9.;
	format start end yymmdd10.;
datalines;
1 27JAN2018	06MAY2019 
2 05MAR2019	09MAR2020
3 31OCT2017	03NOV2019
4 15JAN2018	19JAN2020
5 04JUL2015	12JUL2020
;
run;

[Tom]

If you had to modify the INPUT statement to that code with @ pointers and fixed format input instead of normal LIST MODE input then either your actual data lines have TABS or other strange characters instead of spaces (Which is why jumping over them with @ worked) or you have some missing values that are not demarked with a period.

Note that if you are using Display Manager to edit and submit code then the tabs in the data lines will automatically be replaced by spaces.  But if you are using SAS/Studio to submit the code then this does not happen and you end up with actual TAB characters in the data lines.  You can tell SAS/Studio editor not to insert actual TAB characters into the file by changing your preferences.  You can still hit the TAB key to indent your code, but it will just insert the proper number of spaces to move to the next tab stop instead of messing up your code file with embedded tabs.

Example:

This data step will work (note there is no need to add an extra RUN statement after the end of the data step).

data have; 
  input ID start :date. end :date.;
  format start end yymmdd10.;
datalines;
1 27JAN2018 06MAY2019 
2 .         09MAR2020
3 31OCT2017 03NOV2019
4 15JAN2018 .
5 04JUL2015 12JUL2020
;

But data lines with only spaces will NOT work with list mode input since the INPUT statement will go hunting for a value.

data have; 
  input ID start :date. end :date.;
  format start end yymmdd10.;
datalines;
1 27JAN2018 06MAY2019 
2           09MAR2020
3 31OCT2017 03NOV2019
4 15JAN2018  
5 04JUL2015 12JUL2020
;