BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sathish_jammy
Lapis Lazuli | Level 10

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:

IDHeightWeight
2813019286.2
1547720NULLNULL
291216349.1
11570617183.3
18719NULLNULL
3795716144.5

 

MyTable :

IDhtwtCol3
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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Mar1ene
Obsidian | Level 7

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

View solution in original post

4 REPLIES 4
Mar1ene
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1705 views
  • 0 likes
  • 2 in conversation