BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
Cynthia_sas
SAS Super FREQ
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Patrick
Opal | Level 21
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
deleted_user
Not applicable
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
LinusH
Tourmaline | Level 20
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
deleted_user
Not applicable
You can use hash objects as lookup table in Data step.
http://support.sas.com

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 917 views
  • 0 likes
  • 5 in conversation