I have the following table from which i get the values of the table which i have inserted to monitor. proc sql;
CREATE TABLE LIST_OF_TABLES (TABLE_NAME_COLUMN CHAR(20));
INSERT INTO LIST_OF_TABLES
VALUES("EXAMPLE_TABLE1")
VALUES("EXAMPLE_TABLE2")
VALUES("EXAMPLE_TABLE3");
SELECT LEFT(PUT(COUNT(TABLE_NAME_COLUMN),4.0)) INTO :TABLE_COUNT FROM LIST_OF_TABLES;
SELECT TABLE_NAME_COLUMN INTO :TABLE_NAME1 - :TABLE_NAME&TABLE_COUNT FROM LIST_OF_TABLES;
create table TEMP (last_date_of_month num informat=date9. format=date9.,
date num informat= date9. format= date9.,
status char(20),
table_name char(20));
quit; Here i am iterating the table name from macro. %MACRO MONITORING;
%DO I=1 %TO &TABLE_COUNT;
PROC SQL;
insert into WORK.TEMP (last_date_of_month,date,status)
/*I need to insert table name in a column for each record.
I have a variable &&TABLE_NAME&I in which i have table name and i am iterating and getting each table name and preparing work.temp data set*/
SELECT distinct DT.last_of_month format=Date9.,
DWT.date format=Date9.,
case
when date is NULL then '0'
when date is NOT NULL then '1'
end as status
FROM DATES DT
left join WORK.&&TABLE_NAME&I as DWT
on DT.last_of_month = DWT.date;
run;
QUIT;
%END;
RUN;
%MEND;
%MONITORING; After inserting the table name in to the "LIST_OF_TABLES" i am iterating the table name and inserting it into work.temp table. In the temp table i have three columns "last_date_of_month, date, status " and i need one more column which is "table-name". Here is the full code. %let start_date = %sysfunc(intnx(week,%sysfunc(today()),-2,e),date9.);
%let end_date = %sysfunc(intnx(week,%sysfunc(today()),0,e),date9.);
%let days = intck('day',"&start_date."d,"&end_date."d);
data dates;
do d = 0 to &days.;
first_of_month = intnx('DAY',"&start_date."d,d,'s');
last_of_month = intnx('DAY',"&start_date."d,d,'e');
output;
end;
run;
proc sql;
CREATE TABLE LIST_OF_TABLES (TABLE_NAME_COLUMN CHAR(20));
INSERT INTO LIST_OF_TABLES
VALUES("EXAMPLE_TABLE1")
VALUES("EXAMPLE_TABLE2")
VALUES("EXAMPLE_TABLE3");
SELECT LEFT(PUT(COUNT(TABLE_NAME_COLUMN),4.0)) INTO :TABLE_COUNT FROM LIST_OF_TABLES;
SELECT TABLE_NAME_COLUMN INTO :TABLE_NAME1 - :TABLE_NAME&TABLE_COUNT FROM LIST_OF_TABLES;
create table TEMP (last_date_of_month num informat=date9. format=date9.,
date num informat= date9. format= date9.,
status char(20),
table_name char(20));
quit;
%MACRO MONITORING;
%DO I=1 %TO &TABLE_COUNT;
PROC SQL;
insert into WORK.TEMP (last_date_of_month,date,status)
/*I need to insert table name in a column for each record.
I have a variable &&TABLE_NAME&I in which i have table name and i am iterating and getting each table name and preparing work.temp data set*/
SELECT distinct DT.last_of_month format=Date9.,
DWT.date format=Date9.,
case
when date is NULL then '0'
when date is NOT NULL then '1'
end as status
FROM DATES DT
left join WORK.&&TABLE_NAME&I as DWT
on DT.last_of_month = DWT.date;
run;
QUIT;
%END;
RUN;
%MEND;
%MONITORING;
Can anyone share the inputs.
... View more
