topic Re: Macro to extract table name from main table and query that table in SAS Programming

Macro to extract table name from main table and query that table

David_Billa — Wed, 03 Mar 2021 02:45:38 GMT

I'm trying to create a macro variable called TABLE which holds the value of the variable 'Data_name' from the table Meta. Once the macro variable is created I want to use that macro variable as one of the table name to query.

Ideally I want to accomplish something like this but the value of TABLE should come from META table from the field 'Data_name'. Any help?

/*Macro variable Table is value is from the field Data_name in Meta table*/
Proc sql;
create table aa as select t1.*,t2.Company
from &table. t1 inner join 
meta t2
on (t1.ID=t2.ID)
;
quit;

I've data like this.

data Meta;
  input Id Company :$40. Data_name :$32. ;
cards;
1234 XXX  Customer
5678 YYY  Insurance
3456 ZZZZ Health
;

data Insurance;
  input Id Type $ Segment $;
cards;
5678 NLG AS2
;

data Customer;
  input Id Flag $ Report $ ;
cards;
1234 Yes Test
;

Re: Macro to extract table name from main table and query that table

Kurt_Bremser — Tue, 02 Mar 2021 15:35:11 GMT

Your code won't run, because reporting_purpose is not present in dataset meta.

Rule #1 of making code dynamic: start with working non-dynamic code.

Re: Macro to extract table name from main table and query that table

AMSAS — Tue, 02 Mar 2021 16:27:05 GMT

Hi, it is not clear to me what you want to achieve. Still here's some code that puts the data_name into macro variables.

/* Create Sample Data */
data meta ;
	infile cards ;
	input id:$12. company:$12. data_name:$12.;
cards;
1234	XXX	Customer
5678	YYY	Insurance
3456	ZZZZ Health
;

/* Create Macro Variables */
data _null_ ;
	/* Read meta dataset */
	set meta ;
	/* create macro variable called table<n> assign it the value from the data_name variable */
	call symput("table"||left(putn(_n_,"8.")),data_name) ;
	/* create macro variable tableCnt that is a count of all the observations in meta dataset */
	call symput("tableCnt",putn(_n_,"8.")) ;
run ;

/* Simple macro to print out the values of the macro variables created in step above */
%macro check() ;
	%do i=1 %to &tableCnt ;
		%put table&i = &&table&i ;
	%end ;
%mend ;

/* Call check macro */
%check ;

Re: Macro to extract table name from main table and query that table

David_Billa — Tue, 02 Mar 2021 17:34:39 GMT

@Kurt_Bremser I've corrected the initial post. reporting_purpose is not required and I've removed it in my query and also in the data

Re: Macro to extract table name from main table and query that table

David_Billa — Tue, 02 Mar 2021 17:50:23 GMT

@AMSAS Thank you for the program, but it's not producing the desired results. I've corrected the initial post to have better view. I want the value of macro variable to hold data of data_name.

Re: Macro to extract table name from main table and query that table

Kurt_Bremser — Tue, 02 Mar 2021 18:02:06 GMT

Supply the data in usable form. I'm tired of writing datalines steps for others who seem to be too lazy for that.

Re: Macro to extract table name from main table and query that table

David_Billa — Tue, 02 Mar 2021 18:36:47 GMT

I'm happy to receive untested code. I just looking for logic

Re: Macro to extract table name from main table and query that table

Tom — Tue, 02 Mar 2021 19:31:07 GMT

Here is how you can post your data in a usable form.

data Meta;
  input Id Company :$40. Data_name :$32. ;
cards;
1234 XXX  Customer
5678 YYY  Insurance
3456 ZZZZ Health
;

data Insurance;
  input Id Type $ Segment $;
cards;
5678 NLG AS2
;

data Customer;
  input Id Type $ Segment $ ;
cards;
1234 NL AS1
;

It is really no harder to make this than the version you posted.

So for your example query looks like you want to run these two queries.

select t1.*,t2.Company
from Insurance t1
inner join meta t2
  on t1.ID=t2.ID
  and t2.data_name ="Insurance"
;
select t1.*,t2.Company
from Customer t1
inner join meta t2
  on t1.ID=t2.ID
  and t2.data_name ="Customer"
;

What do you want to do with the results? Do you want to create two separate datasets? Do you want to create one dataset with the results from both queries?

Perhaps you just want to put all of the little tables together and join the result with the META table?


data all;
  length dsname $41 Data_name $32 ;
  set Insurance Customer indsname=dsname;
  Data_name = scan(dsname,-1,'.');
run;

proc sql;
create table want as 
select t1.*,t2.Company
from all t1
inner join meta t2
  on t1.ID=t2.ID
  and upcase(t2.data_name) = upcase(t1.data_name)
;
quit;

Re: Macro to extract table name from main table and query that table

David_Billa — Wed, 03 Mar 2021 02:46:50 GMT

@Kurt_Bremser @Tom has provided us the datastep. I've updated the intial post with the data step code.

@Tom Thank you for the code. But I want the value of TABLE to come from the field 'Data_name' which is available in META table. Assume ID is the common field between the tables Insurance and Customer. Check my initial post.

So I'm looking for code like this,

proc sql;
create table want as 
select t1.*,t2.Company
from <macro_variable> t1
inner join meta t2
  on t1.ID=t2.ID 
;
quit;

Re: Macro to extract table name from main table and query that table

Peter_C — Wed, 03 Mar 2021 07:47:27 GMT

2 step answer (cannot do it in one)
SQL can provide both

Select data_name into : myTable trimmed
From your.metadata
Where whatever
;
Select * from libname.&myTable
Where <other conditions>
;
I think you cannot get the final table/select with a join to metadata because the required table is unknown when the query is compiled. It is only known after run time

Re: Macro to extract table name from main table and query that table

Kurt_Bremser — Wed, 03 Mar 2021 08:25:26 GMT

Since you want to run the query multiple times, you also need to use a variable name for the output dataset, otherwise only the result of the final query will be present.

The easiest way to do this is to pack the SQL query into a CALL EXECUTE:

data Meta;
  input Id Company :$40. Data_name :$32. ;
cards;
1234 XXX  Customer
5678 YYY  Insurance
3456 ZZZZ Health
;

data Insurance;
  input Id Type $ Segment $;
cards;
5678 NLG AS2
;

data Customer;
  input Id Flag $ Report $ ;
cards;
1234 Yes Test
;

data _null_;
set meta;
if exist(data_name)
then call execute("
Proc sql;
create table aa" !! data_name !! " as select t1.*,t2.Company 
from " !! data_name !! " t1 inner join 
meta t2 
on (t1.ID=t2.ID)
;
quit;
");
run;