topic Re: SAS query using excel input in SAS Programming

SAS query using excel input

Escada — Tue, 17 May 2016 15:50:39 GMT

Hi,

I have a very large table which I would like to query on a regular basis, based on several parameters that I predefine. For example, I can identify:

Table: T1

Column 10

Row 05

Which then retrieves me the data for a specific cell following the parameters (coordinates) identified, making use of the code below:

PROC SQL;
CREATE TABLE QUERY_OUTPUT AS
SELECT
t1.VALUE_DECIMAL,
t1.TABLE_ID,
t1.X,
t1.Y
FROM LARGE_TABLE t1
WHERE t1.TABLE_ID ='T1' AND t1.X ='10' AND t1.Y ='05';
QUIT;

However I would like to replace those parameters (in bold) by an excel table(s)/vectors, which can be imported into a SAS dataset(s) and be used as parameters for the query.

Something like:

Table_ID X Y

T1 10 05

T2 15 10

... ... ...

Can somebody help me finding a solution for this?

Thank you.

Fernando

Re: SAS query using excel input

LinusH — Tue, 17 May 2016 20:46:34 GMT

To use a parameter table consider encapsulate your SQL in a macro.
Then call the macro by a call execute in a data step that reads your parameter table.

Re: SAS query using excel input

Escada — Wed, 18 May 2016 08:00:07 GMT

Hi Linus,

Can you elaborate a litle bit more on that?

Do you have an example of code that can guide me a litle bit?

thanks!

Re: SAS query using excel input

LinusH — Wed, 18 May 2016 09:45:56 GMT

There are tons of examples both on these communities and if you care to search SAS documentation.