Using Sas macro to fetch values from one table to another table

[Sathish_jammy]

Hi Community,

I always rework with the task which I given below. So I need a macro to complete the task in future to consume time. 

I have only a basic knowledge in macro. I need our community help to resolve it.

Server Table: ([192.168.11.3].Project.dbo.Table1)  /**** to make it easier I can rename it as Table1 using Proc SQL.***/

But the server name get change depends upon the requirement. [192.168.12.3/13.3/14.3..... and so on] 

Note : Once I complete server1 table[192.168.11.3] I need to run another server [192.168.12.3] to fetch for same variables. 

Server1 Table:

ID	Height	Weight
28130	192	86.2
1547720	NULL	NULL
2912	163	49.1
115706	171	83.3
18719	NULL	NULL
37957	161	44.5

MyTable :

ID	ht	wt	Col3
28130
1547720
2912
115706
18719
37957

Here I want to fetch the data from server table to my table. As of now I perform it by using this query in SQL

The usual sql query is


update MyTable
set ht = b.Height
FROM MyTablea JOIN [192.168.11.3].Project.dbo.Table1 b
ON a.ID = b.ID and ht is null and                             /***(Null or '')***/
b.R_date = (select min(R_date) from [192.168.11.3].Project.dbo.Table1 cc where a.ID = cc.ID)

update MyTable
set wt = b.Weight
FROM MyTablea JOIN [192.168.11.3].Project.dbo.Table1 b
ON a.ID = b.ID and wt is null and 
b.R_Date = (select min(R_date) from [192.168.11.3].Project.dbo.Table1 cc where a.ID = cc.ID)

/*** one more data to fetch from different table***/
update MyTable
set col3 = result_value
FROM MyTable a JOIN (select * from [192.168.11.3].Project.dbo.Table2 union select * from [192.168.11.3].Project.dbo.Table3) b 
ON a.ID = b.ID and col3 is null and
b.date1 = (SELECT min(date1) FROM (select * from [192.168.11.3].Project.dbo.Table2 where ram_id = '123' union select * from [192.168.11.3].Project.dbo.Table3 WHERE ram_id = '123') cc WHERE a.ID= cc.ID)
and ram_id = '123' 

--Table2
ID	ram_id	result_value
28130	abc	123
1547720	xyz	432
2912	fgh	321
--Table3
115706	abc	456
18719	abc	789
37957	lmn	98

Here I get the data from server table of

[192.168.11.3].Project.dbo.Table1/2/3

and insert it to my own table (MyTable).

If you understand the content you may suggest also using base sas macro.

I can convert the server table as a normal dataset.  For me the macro matters not Proc SQL/Base sas macro

These example are just a small loop. but i have a large set of variables to pull from the Master table.

I hope the macro will help to reduce my effort.

Please let me know the macro steps to solve the problem.

Thanks in Advance!

[Mar1ene]

Hi ... uhm ... Sathish_jammy (what should I call you?)

If I understand you correctly, you basically need to include additional variables to your existing table from various servers/tables? And you do this on a regular basis, hence the macro requirement?

Marlene

[Sathish_jammy]

Hi @Mar1ene

You can call me as Sathish.

Yes! Exaclty you are right.

I have a query for 100's of variables to copy data from one to another.

Once I complete extracting data from one table/server, I do rename the table/Server on all the query line.

I think macro will save my time from those kind of a tasks. 

[Mar1ene · Accepted solution]

Good morning from South Africa Sathish,

Maybe you can try the below:

%macro Macro_Name(Server, Table);

update MyTable
set ht = b.Height
FROM MyTable a JOIN &Server..Project.dbo.&Table. b
ON a.ID = b.ID and ht is in ("null","") and                             /***(Null or '')***/
b.R_date = (select min(R_date) from &Server..Project.dbo.&Table. cc where a.ID = cc.ID)

/*SET ONE*/
%Macro_Name('192.168.11.3', 'Table1');

%Macro_Name('192.168.11.3', 'Table2');

%Macro_Name('192.168.11.3', 'Table3');

/*SET TWO*/

%Macro_Name('192.168.12.3', 'Table1');

%Macro_Name('192.168.12.3', 'Table2');

%Macro_Name('192.168.12.3', 'Table3');

Give it a shot... 

Marlene

[Sathish_jammy]

Thanks for the card @Mar1ene. You really made my day!