topic How to insert a table name into a column in SAS Programming

How to insert a table name into a column

priya_05 — Mon, 13 May 2019 11:45:45 GMT

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.

Re: How to insert a table name into a column

Tom — Mon, 13 May 2019 17:51:56 GMT

I don't understand what the question is.

It looks like you have the value in a macro variable. So if you want to use that value in some SAS code, like your SQL statements then it as simple as just enclosing the reference to the macro variable in double quotes.

%let table_name=FRED;

insert into some_other_table (table_name,var1,var2)
select "&table_Name" as table_name
,a.var1
,a.var2
from &table_name A
;

Note that your whole approach seems way too complex, but wasn't your question.

Re: How to insert a table name into a column

tsap — Mon, 13 May 2019 18:25:23 GMT

In the code that your provided, you will need to make updates in two locations in order to have the table name of the current data being reviewed added into a column on your final table as values:

1.Insert Statement:

- CURRENT LOGIC

insert into WORK.TEMP (last_date_of_month,date,status)

- UPDATED LOGIC (Add the table_name variable to the list of variables being inserted into WORK.Temp)

2. Select Statement:

- CURRENT LOGIC

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

- UPDATED LOGIC

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
     , "&&TABLE_NAME&I."     AS table_name

Add the last line, referencing the iteration specific table name macro as the text value to populated in the column, 'table_name'.

Hopefully this helps.

Re: How to insert a table name into a column

priya_05 — Thu, 16 May 2019 09:26:18 GMT

Thanks for the Reply. It works for me .

Thanks again.