Building macro in proc sql step

[niloya]

Hi,

I have more than 10 different data set like the table below. I need to use those 10 different data set to build the query. Would you please help me to build a macro to pull the data set directly via query?  i have 10 different nums table and each of them has 1000 rows. Each num will be used in proc sql step to build another query like below. I would like to set up the query via macro step rather than pulling each query. Would you please help?

nums
102491
127495
145603
146082
151317
164886
164887
164888
169898
171675
174616
174617
176940
177503
184793

Select * from table where num in (num1)

Select * from table where num in (num2)

Select * from table where num in (num3)

Select * from table where num in (num4)

Select * from table where num in (num5)

Select * from table where num in (num6)

Select * from table where num in (num7)

Select * from table where num in (num8)

Select * from table where num in (num9)

Select * from table where num in (num10)

[PaigeMiller]

If these many data sets are the result of your earlier question where you ask how to split the data into many data sets, you are going about this the wrong way, and making life harder for yourself. You really ought to consider keeping everything in one big data set and then you can perform analyses or other tasks using the BY statement, rather than writing loops.

[niloya]

@PaigeMiller  The system does not let me pull in data greater than 1000 rows. That is why i am splitting it to 1000 and then pulling it via query

[Tom]

What system is placing this limit on you?  SAS does not have any such limit.

[niloya]

I am connecting different servers through SAS. It is winsql. 

[Tom]

---

@niloya wrote:

I am connecting different servers through SAS. It is winsql. 

---

Move the list to the other server and then you can reference in the query you push to the other server.

[Kurt_Bremser]

Have that limit removed there. This is plain ridiculous.

---

@niloya wrote:

@PaigeMiller  The system does not let me pull in data greater than 1000 rows. That is why i am splitting it to 1000 and then pulling it via query

---

[Tom]

Do you want to generate code like:

select * from table BIG where num in (select num from NUM1)

Or code like:

select * from table BIG where num in 
(102491
,127495
,145603
)

[niloya]

@Tom  Thank you Tom. i prefered this one.

select * from table BIG where num in 
(102491
,127495
,145603
)

[Tom]

If the BIG table has a lot of columns or multiple observations per value of NUM it is probably going to be a lot easier to instead move the list of numbers to the remote machine so it can be joined there.

If the values are actual numbers like in your example then you should be able to place all 1000 numbers into a single macro variable.

So you could create 10 subsets of your remote database by generating 10 select statements like this:

PROC SQL ;
   connect to ..... as MYDB ..... ;
%do i=1 %to 10 ;
   select num into :list separated by ',' from num&i;
   create table subset&i as select * from connection to mydb
  (select * from BIG where num in (&list)
  );
%end;
quit;

[Reeza]

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[Reeza]

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md