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.
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?
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):
call symput('MyMacroVar',cats("'",myvar,"'"); /* pass value of myvar to macro variable &MyMacroVar */
stop; /* no further iterations through dataset */
where MyField=&MyMacrovar ....
If it's about joining (subselect,...) two tables, one in Oracle and one in SAS then let us know.
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
For C1_Rec in C_Main -- This loops through each record in the returned dataset
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.
I'll work on the SAS equivalent this week and post my example.