topic Re: Building macro in proc sql step in SAS Programming

Building macro in proc sql step

niloya — Wed, 04 Dec 2019 16:26:16 GMT

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)

Re: Building macro in proc sql step

PaigeMiller — Wed, 04 Dec 2019 16:35:33 GMT

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.

Re: Building macro in proc sql step

niloya — Wed, 04 Dec 2019 16:59:57 GMT

@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

Re: Building macro in proc sql step

Tom — Wed, 04 Dec 2019 17:22:13 GMT

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

Re: Building macro in proc sql step

Tom — Wed, 04 Dec 2019 17:24:53 GMT

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
)

Re: Building macro in proc sql step

niloya — Wed, 04 Dec 2019 17:27:02 GMT

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

Re: Building macro in proc sql step

niloya — Wed, 04 Dec 2019 17:28:03 GMT

@Tom Thank you Tom. i prefered this one.

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

Re: Building macro in proc sql step

Tom — Wed, 04 Dec 2019 17:30:03 GMT

@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.

Re: Building macro in proc sql step

Tom — Wed, 04 Dec 2019 17:35:27 GMT

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;

Re: Building macro in proc sql step

Kurt_Bremser — Wed, 04 Dec 2019 17:39:43 GMT

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

Re: Building macro in proc sql step

Reeza — Wed, 04 Dec 2019 18:35:16 GMT

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

Re: Building macro in proc sql step

Reeza — Wed, 04 Dec 2019 18:35:18 GMT