Only export last 6 months of data

[ZDR]

Hello,

I have a dataset of transactions, and I need to export just the last 6 months of it to Excel. The date fields I have are "Post Date" (e.g. 10DEC15), transaction_day (1-31), transaction_month (1-12), and transaction_year (2000-2016). The name of the Post Date field has a space in it, but I don't want to rename it for now (for reasons related to final usage).

I output two files: the first one must contain all the data, and the second only the 6 months. My code is obvious, but here it is anwway:

/* this should contain all the data */

proc export
DATA= WORK.FINAL_OUTPUT
DBMS= XLSX
outfile= "<<output location>>&sysdate..xlsx"
replace;

/* this should contain just the last 6 months of data */

PROC EXPORT
DATA= WORK.FINAL_OUTPUT
DBMS= XLSX
OUTFILE= '<<output location>>'
REPLACE;
RUN;

run;

Thanks!

[ballardw]

You may want to very explicitly describe what you mean by "last 6 months" of data.

First, does your data have a date variable on each record?

Second if I run the program today, 7Oct2016, do you want data from 7Apr2016, 1April2016 or after 30Apr2016? Or something else?

The answer will likely go back to use of the INTNX function compared with the result of the TODAY() function.

Possibly something like

Data WORK.FINAL_OUTPUT;

   set have;

   if date ge Intnx('month',today(),-6,'B');

run;

Where HAVE is your existing data set you want to extract from and DATE is the name of your SAS date valued variable.

The example would select all the records where the date variable is equal to or greater than the first of the month 6 months previous.

[ZDR]

ballardw - thanks for your post.

1) Yes, each entry has a date record. Multiple transactons will have taken place on the same date.

2) Good question. If you run the program today, and have data until the 7th of October, it should give me data from April - September. HOWEVER - and this is important - if you run it today but only have data until, say, July 7th, it should give me all the data from January until June. This is because I don't always have the most up-to-date data. So, to say it clearly - it should give the last 6 complete months of data in the dataset.

I currently have data until the end of June. Your code is giving me data from April 1st until the end of June (the last data i have). If I were to guess, the "today()" is the only thing that needs to change, to reference the last day in the dataset, or the last day of the last complete month in the dataset?

[ballardw · Accepted solution]

As with any programming endeavor the problem has to be broken down into the explicit bits before programming.

Since you are now saying something to the tune of "if the last data ends 4 months before the current date then get the end of the actual data and backup 6 months from there" then that is what you need to develop logic for.

So the steps would be something like:

   Find the last date in the data set  (we did something similar in your other post: HINT: instead of MIN use MAX and resulting value instead of Today() function);

   Get the value 6 months before that (covered)

   Decide which are we actually going to use: 6 months from the data date or 6 months from todays date. If it is always the first case then you're done. If it may be both then you need to state the rules in terms of the data. Or possibly provide a user option as a macro parameter if the choice will be a humans (which may be a good idea to implement in the long wrong for testing and possibly one-off reports).

Since this is from your description part of a macro you can use macro logic to implement branches with %if %then %else.

[ZDR]

Thanks - this worked perfectly!