Date:         Fri, 16 May 2008 18:37:47 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Nested Data Accesses?
In-Reply-To:  <8eb59c34-58b7-4fda-81dc-2a46d7ed93ba@i76g2000hsf.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

In addition to the excellent advice you have already received from
resident SAS SQL experts, I'll chime in with what may see to be random
thoughts. You now know that you cannot mix SAS Data step and SQL syntax,
but have you thought of the idea of "streaming" data values into a Data
step by using a SQL view of a SAS dataset, RDBMS relation, or even an
Excel worksheet? You don't have to SET olddata directly. You can first
create a SAS SQL view of olddata and SET that. Also, if you are really
trying to update a dataset olddata, have you considered a SQL UPDATE? No
need then to assign values.

Since you are assigning values of SAS Macrovariables to data step
variables, you may be attempting to work around the natural order of SAS
Data steps and SQL queries to force data values into something as close
as possible to scalar variables in procedural languages. SAS often balks
at that. The SAS compilers rigidly encapsulate Data steps and SQL
queries. Better to go with the flow of SAS programming and limit
yourself to the closely related dataset and relation data objects.

SAS SQL does support nested data access via in-line queries, but only
within a relational framework in which an inner query must resolve to
the value of an appropriate relvar in the immediate outer query. Often
in SQL database and SAS Data step programming the logical and easy way
to combine data looks strange at first. Take a few minutes to describe
what you are trying to do. (In set-logic programming languages such as
SQL, one is basically doing that for a compiler, as opposed to
specifying how to solve it.) The technique for "declaring" a solution to
your problem may surprise you. Or not. Either way you will gain a deeper
understanding of SAS and SQL. S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Laptop765@gmail.com
Sent: Friday, May 16, 2008 4:09 PM
To: sas-l@uga.edu
Subject: Nested Data Accesses?


Hello.  I'm relatively new to SAS but have a pretty good understanding
of it.  I am having difficulty with something...  I have: data temp; set
olddata;
%getnum(param)
var1=&val1;
var2=&val2;
run;

getnum is a macro whose contents are a proc sql to grab the values that
I need to use to update temp.  SAS claims that var1 and var2 are invalid
statements or out of place (error 180-322).  After much debugging I
discovered that if I remove the call to getnum, it works fine (although
I do not have my val1 and val2).  This seems to indicate that a proc sql
nested inside a data step is not allowed...

Essentially what I need to do is update some data with values calculated
based on existing data in a different set.  Is this even possible?  If
so, does anybody have any suggestions on how to go about doing it?