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 10:38:47 -0400
Reply-To:     Paul Dorfman <sashole@BELLSOUTH.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul Dorfman <sashole@BELLSOUTH.NET>
Subject:      Re: Populating variables
Comments: To: SUBSCRIBE SAS-L Anonymous <randistan69@HOTMAIL.COM>

Randy,

If the arrangement you have shown, i.e. that VARA has a non-missing value on the first record in the file sorted by ID DATE, is guaranteed, then you can simply do the following, where use is being made of the auto-retained and auto-droppable numeric auto-variable _iorc_:

data have ; input date: mmddyy8. id vara ; cards ; 06/01/09 1 1402 06/01/09 1 . 06/01/09 1 . 06/02/09 1 1543 06/02/09 1 . 06/01/09 2 1602 06/01/09 2 . 06/01/09 2 . 06/02/09 2 1755 06/02/09 2 . ; run ;

data need1 ; set have ; by id date ; if first.date then _iorc_ = vara ; else vara = _iorc_ ; run ;

However, "guaranteed" is a dangerous assumption. Even though the order by key variables is guaranteed by means of pre-sorting, one invades a precarious territory by assuming a certain order within the by-groups. For example, for a variety of reasons it may so happen that in a file sorted by ID DATE, the non-missing VARA value does not fall on the first record in the group.

In such cases, it is more robust to process each group twice to guarantee that the condition is met on the first pass and create the needed output on the second. For example, this will work against HAVE sorted by ID DATE regardless where the non-missing VARA value is located within the by-group:

data need2 (drop = _:) ; do _n_ = 1 by 1 until (last.date) ; set have ; by id date ; if not missing (vara) then _nvara = vara ; end ; do _n_ = 1 to _n_ ; set have ; vara = _nvara ; output ; end ; run ;

Or, even more concisely, you can resort to SQL which does not (and cannot) rely on input data ordering, instead presuming that for each ID DATE group, you need the maximal VARA value in the group:

proc sql ; create table need3 (drop = _:) as select id , date , max (vara) as vara , vara as _vara from have group id, date order id, date ; quit ;

Of course, SQL also processes each by-group twice, albeit via a different internal mechanism and behind the scenes.

Kind regards ------------ Paul Dorfman Jax, FL ------------

On Mon, 5 Oct 2009 00:19:20 -0400, Randy <randistan69@HOTMAIL.COM> wrote:

>Dear Joe: >Slight variation of the problem: > >Date ID VarA >06/01/09 1 1402 >06/01/09 1 . >06/01/09 1 . >06/02/09 1 1543 >06/02/09 1 . >06/01/09 2 1602 >06/01/09 2 . >06/01/09 2 . >06/02/09 2 1755 >06/02/09 2 . > >The data set should look like this >Date ID VarA >06/01/09 1 1402 >06/01/09 1 1402 >06/01/09 1 1402 >06/02/09 1 1543 >06/02/09 1 1543 >06/01/09 2 1602 >06/01/09 2 1602 >06/01/09 2 1602 >06/02/09 2 1755 >06/02/09 2 1755 > >Thank You


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