|Date: ||Mon, 23 Nov 1998 15:23:02 +0000|
|Reply-To: ||Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>|
|Sender: ||"SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>|
|From: ||Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>|
|Subject: ||Re: File Manipulation using Merge?|
In article <firstname.lastname@example.org>, David Collins
>Here is an easy one, hopefully:
>Does anyone know the best way to attempt to remove records out of a SAS
>dataset based on a variable, call it "STATUS". STATUS can be
>'READY','STARTED','RESTARTED', and 'FINISHED' and these records are
>grouped by a "MASTERKEY". I need to see the last STATUS for this record
>grouping provided they didn't go FINISHED. I can get part of this with
>PROC SORT and use 'BY' MASTERKEY STATUS to show LAST.MASTERKEY records.
>Unfortunately, this shows STATUS of records that are of the FINISHED
>grouping, as well. I cannot just remove FINISHED because the other
>records created up to that point show themselves in the LAST.MASTERKEY
>results. FIRST. and LAST. is simple and I was hoping it stay that way.
>I created 2 separate files: fin being only records that were FINISHED.
>I tried sorting both notfin and fin by MASTERKEY and STATUS and then
>using a merge:
> merge notfin(in=a) fin(in=b);
> by masterkey;
> if a and not b;
>Any suggestions would be appreciated!
If the underlying data were simply status, I would retain the statuses
in a set of variables, just one for each status, and at last.masterkey,
if "finished" hadn't been found, I would loop, outputing an obs for each
But I suspect you have more variables than just status.
The following datastep, allows for any number of status observations
(including repeats) as well as any number of variables.
how about something simple
Process your data twice,
first: just to uncover "finished" cases (in=prelim)
2nd: delete if "finished" found for the masterkey
assume your original data set containing variable status is "allstate"
drop fin ; * "finished"-is-present signal;
by masterkey; /* interleaving data at bygroup level*/
if first.masterkey then fin=0;
if prelim then do; /* signify occurrence of "finished" status
on first pass through masterkey bygroup*/
fin+( status='FINISHED' );
IF not fin ; /* haven't seen "finished" status in this masterkey*/
/* of course I haven't tested this, so, good luck */