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
>>
>
>
>