How to automatically extract data from a database for the previous month

[mmohotsi]

Hi Everyone

I need to automatically extract data from the database on a specific day (5th of each month) for the previous  month. 

I thought of using the following macros for dates but the problem is the months days are not constant

%let Month_end_date = %sysfunc(putn(%sysfunc(intnx(day, %sysfunc(today()), -5, same)), date9.));
%let Month_Begin_date = %sysfunc(putn(%sysfunc(intnx(day, %sysfunc(today()), -34, same)), date9.)); 

This will only be applicable for the February in a leap year.

Please help

[PaigeMiller]

Use the INTNX function differently. SAS has done the hard work of figuring out what the starting and ending dates of each month are, leap year or not. So you don't have to figure it out somehow.

Do NOT format macro variables when using them to extract data from data bases and the macro variable represent days. These values are the number of days since 01JAN1960, which is how SAS represents days and will work when you extract from the database. Using values like 01JAN2024 will not work when extracting data from a database.

%let today=%sysfunc(today());
%let Month_end_date = %sysfunc(intnx(month, &today, -1, e));
%let Month_Begin_date = %sysfunc(intnx(month, &today, -1, b));

%put &=today;
%put &=month_end_date;
%put &=month_begin_date;

[mmohotsi · Accepted solution]

Thank you 

The solution worked

I just formatted the values to date by including a date9. just before the last closing parenthesis:

%let Month_end_date = %sysfunc(intnx(month, &today, -1, e));
%let Month_Begin_date = %sysfunc(intnx(month, &today, -1, b));

 Thank you so much once again PaigeMiller

[PaigeMiller]

@mmohotsi 

If you are using these values to extract data from databases, formatting these macro variables is not the correct thing to do. You would use the macro variables un-formatted.

[mmohotsi]

Hi Paigemiller
As a SAS novice user, I am unable to maneuver on the code to get it right, hence my request to the community.

[PaigeMiller]

Saying that you can't get it right provides us no information that would help us solve your problem.

If there are ERRORs in the log, show us the log. If there are no errors but the results are wrong, show us your code and the incorrect results.

[mmohotsi]

The code is as follows
filename EXECU1
TEMP;

%macro Raw_Data;
data _null_;
file EXECU1;
set CUSTOMER_FRAME33;
IF REPORT_NAME = 'ZUIKERBOSCH_DR' THEN DO;

put "proc sql; " / "create table " Data1 " as " / " select SAMPLE.SAMPLE_NUMBER, SAMPLE.PARENT_ALIQUOT, SAMPLE.SAMPLING_POINT, SAMPLE.ORIGINAL_SAMPLE,
SAMPLE.SAMPLED_DATE, RESULT.NAME, RESULT.FORMATTED_ENTRY,RESULT.UNITS,
RESULT.NUMERIC_ENTRY, RESULT.STATUS,RESULT.REPORTABLE, RESULT.RESULT_NUMBER";
put " from datasource1.sample, datasource1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between ";
put "'" start_date +(-1) "'d and" ;
put "'" end_date "'d";
put "and sample.sampling_point in " SAMPLING_POINTS" and " / " RESULT.name in " DETERMINANDS" and " ;
put "result.status = 'A' and " / " result.reportable = 'T' and " / " parent_aliquot ne 0;" ;

;END;ELSE
IF REPORT_NAME = 'ZUIKERBOSCH_RD' THEN DO;

put "proc sql; " / "create table " Data1 " as " / " select SAMPLE.SAMPLE_NUMBER, SAMPLE.PARENT_ALIQUOT, SAMPLE.SAMPLING_POINT, SAMPLE.ORIGINAL_SAMPLE,
SAMPLE.SAMPLED_DATE, RESULT.NAME, RESULT.FORMATTED_ENTRY,RESULT.UNITS,
RESULT.NUMERIC_ENTRY, RESULT.STATUS,RESULT.REPORTABLE, RESULT_NUMBER";
put " from datasource2.sample, datasource2.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between ";
put "'" start_date +(-1) "'d and" ;
put "'" end_date "'d";
put "and sample.sampling_point in " SAMPLING_POINTS" and " / " RESULT.name in " DETERMINANDS" and " ;
put "result.status = 'A' and " / " result.reportable = 'T' and " / " parent_aliquot ne 0;" ;

;END;

%include EXECU1;
%mend Raw_Data;
%Raw_Data;


The log is as follows:


1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='POC';
5 %LET _CLIENTPROJECTPATH='C:\Users\imstats\Documents\Disater_Implememtation\SAS_Programmes\POC\ZB_POC\ZB_POC_PROJECT.egp';
6 %LET _CLIENTPROJECTPATHHOST='IMSAS-C7704';
7 %LET _CLIENTPROJECTNAME='ZB_POC_PROJECT.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 ODS LISTING GPATH=&sasworklocation;
18 FILENAME EGHTML TEMP;
19 ODS HTML5(ID=EGHTML) FILE=EGHTML
20 OPTIONS(BITMAP_MODE='INLINE')
21 %HTML5AccessibleGraphSupported
22 ENCODING='utf-8'
23 STYLE=HTMLBlue
24 NOGTITLE
25 NOGFOOTNOTE
26 GPATH=&sasworklocation
27 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28 FILENAME EGPDF TEMP;
29 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=Pearl;
NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF".
30 FILENAME EGSR TEMP;
31 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
32 STYLE=HTMLBlue
33 NOGTITLE
34 NOGFOOTNOTE
35 GPATH=&sasworklocation
36 ENCODING=UTF8
37 options(rolap="on")
38 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
39 FILENAME EGXLSSX TEMP;
40 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel
41 OPTIONS (
42 EMBEDDED_TITLES="yes" EMBEDDED_FOOTNOTES="yes"
43 );
WARNING: Unsupported device 'SVG' for EXCEL(EGXLSSX) destination. Using default device 'PNG'.
44
45 filename EXECU1
46 TEMP;
47
48 %macro Raw_Data;
49 data _null_;
50 file EXECU1;
51 set CUSTOMER_FRAME33;
52 IF REPORT_NAME = 'ZUIKERBOSCH_DR' THEN DO;
53
54 put "proc sql; " / "create table " Data1 " as " / " select SAMPLE.SAMPLE_NUMBER, SAMPLE.PARENT_ALIQUOT, SAMPLE.SAMPLING_POINT, SAMPLE.ORIGINAL_SAMPLE,
55 SAMPLE.SAMPLED_DATE, RESULT.NAME, RESULT.FORMATTED_ENTRY,RESULT.UNITS,
56 RESULT.NUMERIC_ENTRY, RESULT.STATUS,RESULT.REPORTABLE, RESULT.RESULT_NUMBER";
57 put " from datasource1.sample, datasource1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between ";
58 put "'" start_date +(-1) "'d and" ;
59 put "'" end_date "'d";
60 put "and sample.sampling_point in " SAMPLING_POINTS" and " / " RESULT.name in " DETERMINANDS" and " ;
61 put "result.status = 'A' and " / " result.reportable = 'T' and " / " parent_aliquot ne 0;" ;
62
63 ;END;ELSE
64 IF REPORT_NAME = 'ZUIKERBOSCH_RD' THEN DO;
65
66 put "proc sql; " / "create table " Data1 " as " / " select SAMPLE.SAMPLE_NUMBER, SAMPLE.PARENT_ALIQUOT, SAMPLE.SAMPLING_POINT, SAMPLE.ORIGINAL_SAMPLE,
67 SAMPLE.SAMPLED_DATE, RESULT.NAME, RESULT.FORMATTED_ENTRY,RESULT.UNITS,
68 RESULT.NUMERIC_ENTRY, RESULT.STATUS,RESULT.REPORTABLE, RESULT_NUMBER";
69 put " from datasource2.sample, datasource2.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between ";
70 put "'" start_date +(-1) "'d and" ;
71 put "'" end_date "'d";
72 put "and sample.sampling_point in " SAMPLING_POINTS" and " / " RESULT.name in " DETERMINANDS" and " ;
73 put "result.status = 'A' and " / " result.reportable = 'T' and " / " parent_aliquot ne 0;" ;
74
75 ;END;
76
77 %include EXECU1;
78 %mend Raw_Data;
79 %Raw_Data;
WARNING: Physical file does not exist, C:\Users\imstats\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-3604-fd8ff039\contents\SAS Temporary Files\_TD16136_IMSAS-C7704_\#LN03879.
ERROR: Cannot open %INCLUDE file EXECU1.
80
81 %LET _CLIENTTASKLABEL=;
82 %LET _CLIENTPROCESSFLOWNAME=;
83 %LET _CLIENTPROJECTPATH=;
84 %LET _CLIENTPROJECTPATHHOST=;
85 %LET _CLIENTPROJECTNAME=;
86 %LET _SASPROGRAMFILE=;
87 %LET _SASPROGRAMFILEHOST=;
88
89 ;*';*";*/;quit;run;
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


90 ODS _ALL_ CLOSE;
NOTE: ODS PDF(EGPDF) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)
NOTE: Writing EXCEL(EGXLSSX) file: C:\Users\imstats\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-3604-fd8ff039\contents\SAS Temporary Files\_TD16136_IMSAS-C7704_\#LN03856
91
92
93 QUIT; RUN;
94

Regards
MMohotsi

[PaigeMiller]

Thank you

ERROR: Cannot open %INCLUDE file EXECU1.

This has nothing to do with your macro variables. You are trying to use a file that has not been properly identified.

SAS does not know what EXECU1 is. You need a RUN; to close the DATA step, and then the file is created properly and will then SAS will be able to %include it.

When you create macro variables to contain dates, you still have to use proper SAS code.

put "'" start_date +(-1) "'d and" ;
put "'" end_date "'d";

Start_date and end_date are not variables, they are macro variables. So you want to include an ampersand before the variable name. In addition, if the macro variables are unformatted as I suggested, then you want

put "&start_date and &end_date";

How simple is that? I am not sure why you are adding -1 in your code, but if you think that's still needed, modify the code above to subtract 1.

Question: why is this a macro anyway? Why not just have DATA _NULL_ followed by %include outside of a macro?

[mmohotsi]

Hi PaigeMiller
The variables start_date , end_date, Determinands, and Sampling_points are from the staging table

Regards
MMohotsi

[PaigeMiller]

So where are the macro variables used?

What is a typical value of start_date and end_date? Is it numeric or character according to PROC CONTENTS? How is it formatted according to PROC CONTENTS?