Need to set Table name in to the column for each record

priya_05 — Sun, 12 May 2019 15:12:41 GMT

I have one program in which i need to monitor the table on daily basis.

I have created a table where i am inserting table names which i need to monitor.

CREATE TABLE LIST_OF_TABLES (TABLE_NAME_COLUMN CHAR(20));

   INSERT INTO LIST_OF_TABLES
   			VALUES("EXAMPLE_TABLE1")
			VALUES("EXAMPLE_TABLE2")
			VALUES("EXAMPLE_TABLE3");

Now i have written a macro to iterate the table "LIST_OF_TABLES" and select some columns from each table and prepare TEMP data set.

here is the 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 veriable &&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;

I have commented in the program where i need to insert / add column of table name.

For reference 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 veriable &&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;

Re: Need to set Table name in to the column for each record

ChrisNZ — Tue, 14 May 2019 03:47:58 GMT

The question is unclear. Like this?

proc sql;
  insert into WORK.TEMP (LAST_DATE_OF_MONTH,DATE,STATUS)  
  select distinct DT.last_of_month           format=date9.
                 ,DWT.date                   format=date9.
                 ,"&&TABLE_NAME&I"   as TABLE
                 ,(DATE is not NULL) as STATUS
  from DATES DT
  left join WORK.&&TABLE_NAME&I as DWT
  on DT.last_of_month = DWT.date;

topic Need to set Table name in to the column for each record in SAS Programming

Need to set Table name in to the column for each record

Re: Need to set Table name in to the column for each record