topic Re: Macro Question...Using dataset observation values in Proc SQL in SAS Programming

Macro Question...Using dataset observation values in Proc SQL

deleted_user — Mon, 27 Oct 2008 19:16:45 GMT

Greetings. I am relatively new to SAS (but have a good deal of experience in Oracle PL\SQL).
Is it possible to,
1. Loop through a SAS Dataset
2. Assign one of the observation values , say Customer #, to a variable
3. Execute a macro (passing that Customer # variable) that performs a PROC SQL against an Oracle/Teradata database table and returns the information to a SAS dataset and/or variable

I've done some research on this and it appears that it can be done using the SYMPUT function. But I wanted to confirm with a user group first.
I know how to do this using PL/SQL, but nor SAS.

Any advice is appreciated.

Thanks!
Troy

Re: Macro Question...Using dataset observation values in Proc SQL

Cynthia_sas — Tue, 28 Oct 2008 03:43:00 GMT

Hi:
My suggestion is to take a look at the SAS Macro Facility, specifically the way that CALL EXECUTE works. The documentation is here
http://support.sas.com/documentation/cdl/en/mcrolref/59526/HTML/default/a000543697.htm

and there are 2 relevant examples in the documentation.

cynthia

Re: Macro Question...Using dataset observation values in Proc SQL

sbb — Tue, 28 Oct 2008 03:49:38 GMT

Also consider the RESOLVE function within the DATA step facility, however I am unclear about your stated interest - is it that you want to invoke PROC SQL for each DATA step pass as you are creating observations? The phrasing of your post is unclear about how/when you want to perform the stated objective -- that being serially or each/all tasks with each pass of your data?

Scott Barry
SBBWorks, Inc.

Re: Macro Question...Using dataset observation values in Proc SQL

Patrick — Tue, 28 Oct 2008 07:01:17 GMT

If it's about passing a single value as a constant to an SQL statement then consider something like the following (more or less the approach you drafted):

data _null_;
set yourtable;
if then
do;
call symput('MyMacroVar',cats("'",myvar,"'"); /* pass value of myvar to macro variable &MyMacroVar */
stop; /* no further iterations through dataset */
end;
run;

proc sql;
....
where MyField=&MyMacrovar ....
....
quit;

If it's about joining (subselect,...) two tables, one in Oracle and one in SAS then let us know.

HTH
Patrick

P.S. Just in case you don't know this one already:
options sastrace=',,,d' sastraceloc=saslog;
http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a000433982.htm Message was edited by: Patrick

Re: Macro Question...Using dataset observation values in Proc SQL

deleted_user — Wed, 29 Oct 2008 17:20:01 GMT

Scott,
I want to perform the lookup for each observation in the dataset;for each pass of the data.

So, for example, if I had a dataset with 50 customers, I will perform a PROC SQL query based on one or more of the columns for each of the 50 observation dataset.

In PL\SQL, I could achieve this with code resembling the following.

Cursor C_Main is
Select CustomerID, ProductID, PurchaseDate
from SomeTable;

For C1_Rec in C_Main -- This loops through each record in the returned dataset
Loop
1. Perform Lookup information from other dataset(s).
2. Assign variables, etc based upon business logic
3. Update another table with variables values assigned in step 2.
End loop;

I'll work on the SAS equivalent this week and post my example.

Thanks for your help.
Troy

Re: Macro Question...Using dataset observation values in Proc SQL

LinusH — Wed, 29 Oct 2008 18:16:20 GMT

If you can describe more in detail what end result you want, maybe we could give suggestions for other approaches that haven't thought of. Some sample input and input data could help.

/Linus

Re: Macro Question...Using dataset observation values in Proc SQL

deleted_user — Wed, 29 Oct 2008 19:31:04 GMT

You can use hash objects as lookup table in Data step.
http://support.sas.com