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 (October 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 5 Oct 2009 11:56:21 -0700
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: Assigning the value of one variable to another
Comments: To: Andrew.Clapson@STATCAN.GC.CA
In-Reply-To:  A<F433BF18D811E745B79191E749A6DAF10151ED8F@stcem16.statcan.ca>
Content-Type: text/plain; charset="us-ascii"

Hi Andrew,

SQL has COALESCE and can join other rows:

proc sql; create table DS2 as select a.var1, a.var2, a.var3, coalesce(a.date,b.companydate) as date a.var5 from DS1 as a left join DS1 as b on a.thekeys eq b.thekeys and a.status eq 03 ; quit;

When a.date is missing then argument number two in the COALESCE gets used. If the left join only has data for status=03 then it should only occur if that condition is met.

You will have to round out the code with your real variable names and expand thekeys to be your actual set of variables that you need to join like records on.

Hope this is helpful.

Mark Terjeson Investment Business Intelligence Investment Management & Research Russell Investments 253-439-2367

Russell Global Leaders in Multi-Manager Investing

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Andrew Clapson Sent: Monday, October 05, 2009 11:42 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Assigning the value of one variable to another

Hello SAS list,

I am trying to figure out how to take a variable and give it the value (i.e. copy the value) of another observation in the dataset.

Here's the skinny:

I have an identifier variable ('ID'), a status code ('Status'), and date of birth ('Date'), let's say. I want each observation to have a value for 'Date', but some are missing. Of the observations missing a value for the 'Date' variable, those with the 'Status' of "03" should have the same date of birth - 'Date' - as another observation in the dataset. (These data aren't people, they're companies, and some companies are 'children' of others, so I can copy the 'Date' value from the parent company).

I got about as far as this with the code:

DATA DS2; SET DS1; IF 'Status' = 03 THEN DO; IF 'ID' IN (#1,#2,#2) THEN 'Date' = ('Date' value from observation w/ ID #4) ??? END; RUN;

What I want to do is: - for all observations with a 'Status' of "03", make the value of the 'Date' variable equal to that of its parent company. I have a list of the 'ID' numbers, so it is simple to tell which parents correspond to which children, but what procedure would I use?

Is there a command and/or an elegant way to assign values to specific observations like this?

Thanks for your help,

Andy Clapson


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