BookmarkSubscribeRSS Feed
aperansi
Quartz | Level 8

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?

 

4 REPLIES 4
AMSAS
SAS Super FREQ

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
Quartz | Level 8

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

Reeza
Super User

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
SAS Super FREQ

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) ;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1021 views
  • 2 likes
  • 3 in conversation