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:         Tue, 6 Oct 2009 13:17:08 -0500
Reply-To:     Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Subject:      Re: Assigning the value of one variable to another
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=response

----- Original Message ----- From: "Kevin Myers" <KevinMyers@austin.rr.com> To: <Andrew.Clapson@statcan.gc.ca> Sent: Tuesday, October 06, 2009 13:16 Subject: Re: [Released Spam | pourriel affranchi] Re: Assigning the value of one variable to another

> Hi Andy, > > No problem re the question; everybody has to learn somehow... > > Notice the clause that says: > > left join ds1 as child, ds1 as parent > > This clause says to take all all rows from the ds1 table, treating them as > if the name of that table was "child", and find the matching rows also > from the ds1 table, treating them in this second instance as if the name > of that table was "parent". Which rows match is determined by the ON > clause. > > child.ID says to use the ID variable from the data set referenced as > child. In your case, the same dataset was referenced twice, once as > parent, and once as child. So child.ID and parent.ID both refer to the > same variable in the original data set, but from different rows in the > same data set. The key that determines which rows are involved is the > "ON" clause. By saying "on parent.ID=child.parent_ID, we are saying to > compare the value of the ID variable from the instance of the data set > referred to / acting as parent companies, to the value of the parent_ID > variable from records of the same data set referred to /serving as the > child companies, and only keep those rows where there is a match. It is > as if you made two copies of the original data set, and called one copy > parent, and the other copy child. > > Does that help? > > s/KAM > > p.s. - Be sure to use the second version that I sent you, which uses "left > join" and "on", rather than "where". Otherwise you won't get rows for > companies that don't have parents. Also, I am copying the list so that > others might benefit from this answer. > > > ----- Original Message ----- > From: <Andrew.Clapson@statcan.gc.ca> > To: <KevinMyers@austin.rr.com> > Sent: Tuesday, October 06, 2009 08:40 > Subject: RE: [Released Spam | pourriel affranchi] Re: Assigning the value > of one variable to another > > > Thanks for the responses, Kevin. > I know very little of SQL...although the more I learn about it, I > realize what kind of things it can do that regular SAS statements > can't... > > One question about your submitted code - I used different names for my > variables than I actually have in my dataset, and I am unsure about they > 'child.___'. > > When you write: > > proc sql noprint; > create table ds2 as > select > child.ID, > child.Status, > ETC ETC.... > > What does the 'child.ID' do, exactly? Are you calling each ID number > that has an ID variable value of 'child'?? Sorry if this is an ignorant > question.. > > Thanks, > > Andy > > -----Original Message----- > From: Kevin Myers [mailto:KevinMyers@austin.rr.com] > Sent: October 5, 2009 9:25 PM > To: Andrew.Clapson@statcan.gc.ca; SAS-L@LISTSERV.UGA.EDU > Subject: [Released Spam | pourriel affranchi] Re: Assigning the value of > one variable to another > > > Untested, but should be close to what you need: > > proc sql noprint; > create table ds2 as > select > child.ID, > child.Status, > case > when Status="03" then coalesce(child.Date,parent.Date) > else child.Date > end as Date > from ds1 as parent, ds1 as child > where child.parent_ID=parent.ID > order by child.ID; > quit; > > You could also use a merge statement in a data step to accomplish the > same > result, but it would be a bit more of a pain to deal with renaming > conflicting variables between the two instances of ds1 that are being > merged. > > HTH, > s/KAM > > > ----- Original Message ----- > From: "Andrew Clapson" <Andrew.Clapson@STATCAN.GC.CA> > To: <SAS-L@LISTSERV.UGA.EDU> > Sent: Monday, October 05, 2009 13:42 > 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