Date: Tue, 1 Jul 2008 13:29:52 -0500
Reply-To: Mary <mlhoward@avalon.net>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Mary <mlhoward@AVALON.NET>
Subject: Re: Update info in data set based on other data set
Content-Type: text/plain; charset="iso-8859-1"
Note Sigurd is only looking at ID here; Jan was asking about a match both on ID and Date.
-Mary
----- Original Message -----
From: Sigurd Hermansen
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, July 01, 2008 12:51 PM
Subject: Re: Update info in data set based on other data set
Jan:
To augment other helpful responses,
proc sql ;
create table c as
select a.*,
case when a.id=b.id and a.date=b.date
then 0
else 1
end as varx
from datasetA as a, datasetB as b; << This "Cartesian join" pairs each
and every tuple (row) in A to each and every tuple in B
quit;
What you are asked for translates in SQL syntax to
SELECT A.*,<case statement>
FROM A LEFT JOIN B /* all tuples in A with a varx value of 1 */
WHERE A.ID=B.ID.
;
The case statement must assign a 0 to attribute varx where the WHERE
condition A.ID=B.ID fails, else a 1.
How do we know that the WHERE condition has failed? In that case B.ID
will have a NULL value. So
CASE WHEN B.ID IS NULL THEN 0
ELSE 1
END AS varx
S
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jan
Sunde
Sent: Tuesday, July 01, 2008 9:27 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Update info in data set based on other data set
Hi
I have a problem as follows :
I want to create a dataset C that copies all of data set A
as well as adds an additional variable (varx) as 0/1 based on whether an
observation id in dataset A occurs in dataset B (on the same date). I
have tried a PROC SQL as follows but with no success:
proc sql ;
create table c as
select a.*,
case when a.id=b.id and a.date=b.date
then 0
else 1
end as varx
from datasetA as a, datasetB as b;
quit;
I get a timeout (OUT OF RESOURCES error).
Any idea on what I am doing wrong here?
