Help with Date Variable

[aperansi]

I have a program i created to get data from the beginning of the month, until the end of the month starting 2 years ago. I run this report for several different vendors, and all vendors have different beginning and end dates.

For now, I have coded the report to run from the 1st through the 1st of the next month.

Some vendors run through the 1st through the 30th, and some vendors run from the 25th through the 26th of any given month.

Could someone help me determine a better way to code this so that I dont have to keep changing the dates around?

[AMSAS]

Hi,

How about creating another table with columns for Vendor, Start Day, End Day. e.g

Vendor Start End

AAA    01    30

BBB    26    25

CCC    01    EOM (EOM would represent last day of the month e.g. Feb 28, Feb 29, Apr30, Dec31)

Then using that table calculate your start/end dates for each vendor

[aperansi]

That is a great idea! How would i go about putting in the calculation for each individual vendor?

[Reeza]

I would change your process so the code specifies the vendor at the top of the program. 
Then you would query this table, usually called a control table, and pull the day values needed into two macro variables. 

Then use INTNX to increment appropriately, as needed. Since you have datetime variables your interval is likely to be DTMONTH and look at the last parameter for INTNX which allows you to align the dates to start, end or same day of the month. 

Note that you may want to specify your control table slightly differently, since some months have 28, 30, 31 days and you'll need to account for that. Since they'll be macro variables, you can keep it as a character/text column.

---

@aperansi wrote:

That is a great idea! How would i go about putting in the calculation for each individual vendor?

---

[AMSAS]

Here's some code that should get you started. You will need to make changes to it to fit your situation, but it should be enough to get you going

data runDates ;
	infile cards ;
	input 
		vendor		$
		startDDStr	$
		endDDStr	$ 
	;
cards ;
AAA 01 30
BBB 26 25
CCC 01 EOM
DDD 01 15
;
run ;

data startEndDates ;
	/* Use todays date as the run date */
	runDate=today() ;
	runDD=day(runDate) ;
	runMM=month(runDate) ;
	runYY=year(runDate) ;
	
	/* If current is not Dec, then end month is next month */
	if runMM ne 12 then do ;
		endMM=runMM+1 ;
		endYY=runYY ;
	end ;
	/* If current month is Dec, then end month is Jan next year */
	else do ;
		endMM=1 ;
		endYY=runYY+1 ;
	end ;
	
	/* read the test data */
	set runDates ;

	/* Convert the start date to a numeric */
	startDD=inputn(startDDStr,"2.") ;	
	/* Create a SAS date value for the start date */
	startDate=MDY(runMM,startDD,runYY) ;
	/* If the endDDStr is not EOM */
	if endDDStr ne "EOM" then do ;
		/* Convert endDDStr to a numeric */
		endDD=inputn(endDDStr,"2.") ;	
		/* Create a SAS date value for the end date */
		endDate=MDY(endMM,endDD,endYY) ;
	end ;
	/* If the endDDStr is EOM */
	else do ;
		/* take the run date, and advance it to the end of the next month */
		endDate=INTNX("month1",rundate,0,"E") ;
		* put endDate= date7. ;
	end ;
	put "--> " vendor startDate date7. " " endDate date7. ;
	/* Create macro variables to contain the start & end dates */
	call symput("startDate"!!left(putn(_n_,"8.")),putn(startDate,"date.")) ;
	call symput("endDate"!!left(putn(_n_,"8.")),putn(endDate,"date.")) ;
	call symput("cntr",putn(_n_,"8.")) ;
run ;

/* Macro to report the start and end dates to the log */
%macro report(cntr) ;
	%do i=1 %to &cntr ;
		%put StartDate&i : &&startDate&i EndDate&i : &&EndDate&i ;
	%end ;
%mend ;
	
%report(&cntr) ;