DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 0

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

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
SAS Super FREQ
Posts: 8,743

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

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
Super Contributor
Super Contributor
Posts: 3,174

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

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.
Respected Advisor
Posts: 3,892

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

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
N/A
Posts: 0

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

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
Super User
Posts: 5,257

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

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
Data never sleeps
N/A
Posts: 0

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

You can use hash objects as lookup table in Data step.
http://support.sas.com
Ask a Question
Discussion stats
  • 6 replies
  • 208 views
  • 0 likes
  • 5 in conversation