LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2008, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Tue, 1 Jul 2008 13:51:20 -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: Update info in data set based on other data set
Comments:   To: Jan Sunde <jan.sunde@GMAIL.COM>
In-Reply-To:   <200807011327.m61AkI24002955@malibu.cc.uga.edu>
Content-Type:   text/plain; charset="us-ascii"

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?


Back to: Top of message | Previous page | Main SAS-L page