I am trying to extract data from a data source given conditions from a staging table. The staging table has more than 1 record and I therefore have to select the appropriate data source between data sources.   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_NUMBER";    put " from datasource1.sample, VGSQL1.datasource1" / " 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;      I get two errors as follows: ERROR: Cannot open %INCLUDE file EXECU1.   ;*';*";*/;quit;run; ____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.   Please help   ... View more

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 ... View more

I have created two macros using PROC SQL from a table attached as follows proc sql; create table March_Quarter_Begin as select March as MarchStart from prototype where Milestone = 'Quarter_Start'; select cats('',put(MarchStart,WORDDATX19.),'') into : Kotara_start from March_Quarter_begin; quit;run;   proc sql; create table March_Quarter_end as select March as MarchEnd from prototype where Milestone = 'Quarter_Stop'; select cats('',put(MarchEnd,WORDDATX19.),'') into : Kotara_end from March_Quarter_end; quit;run;   I then created a pdf report using the code below.   ods pdf file = "&TMC_Report\Weekly_TMC_Performance_Report.pdf_file" notoc; ods text = "Quarter performance: &Kotara_start to &Kotara_end"; Proc report data=Prototype; run; The results are in the pdf file attached.   The problem is that the dates are showing on two rows instead of a single row in the report.   Is there something I am not doing correct?   Please assist?   Regards MMohotsi         ... View more

I am trying to extract dat using two dates from the attached table. the program is as follows:   proc sql; create table Date_July_start as select July as July1 from date1 where 'Milestone No.'n = 1; select July1 into :Monthbegin from Date_July_start; quit; %let Monthbegin=%sysfunc(inputn(&Monthbegin,date9.)); proc sql; create table Date_July_end as select July as July2 from date1 where 'Milestone No.'n = 18; select July2 into :Monthend from Date_July_end; quit; %let Monthend=%sysfunc(inputn(&Monthend,date9.));   In finally extracting data, the data has no records. But if I replace for an example &Monthend with a hardcoded date in a date9. format then data shows expected records. proc sql; create table test2 as select sample.parent_aliquot, sample.sampling_point,sample.sampled_date, result.name, result.numeric_entry,result.status from <database> where sample.sample_number = result.sample_number and sampled_date between &Monthbegin and &Monthend and sample.sampling_point = 'M-Build' ; run; ... View more