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 2011, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 7 Oct 2011 08:29:51 -0400
Reply-To:     "Bian, Haikuo" <HBian@FLQIO.SDPS.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Bian, Haikuo" <HBian@FLQIO.SDPS.ORG>
Subject:      Re: keep the first time record
Comments: To: Stanley Luo <shanminglo@GMAIL.COM>
In-Reply-To:  <201110071032.p974Fxjs012464@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Hi,

In general, you will need 2 steps. 1. Get the first funding_date for each Client_No. 2. Merge back to original data. They can be done within one or two data steps or proc sql.

Something like this will be conventional:

data have; infile cards; input obs Client_No :$10. funding_date :yymmdd10. Acct_nbr :$10.; format funding_date yymmdd10.; cards; 1 A2208xxxxx 2002/10/29 0103xxxA 2 A2208xxxxx 2002/10/29 0103xxxB 3 A2208xxxxx 2004/6/9 0103xxxC ;

/*1. Data step: two step approach*/

data pre_want; set have (sortedby=Client_No funding_date); by Client_No funding_date; if first.client_no; run;

data want; merge have pre_want(in=a); by Client_No funding_date; if a; run;

/*2. Data step: one step approach*/ data want (drop=fd); retain fd; do until (last.client_no); set have (sortedby=Client_No funding_date) ; by Client_No funding_date; if first.client_no then fd=funding_date; end;

do until (last.client_no); set have (sortedby=Client_No funding_date) ; by Client_No funding_date; if fd=funding_date then output; end;

run;

/*3. SQL approach:*/ proc sql; create table want (drop=cn dt) as select * from have right join (select Client_No as cn, min(funding_date) as dt from have group by Client_No) b on have.client_no=b.cn and have.funding_date=b.dt; quit;

I am sure there are many other approaches that others will be presenting. Here is just my 2cents.

HTH, Haikuo

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Stanley Luo Sent: Friday, October 07, 2011 6:33 AM To: SAS-L@LISTSERV.UGA.EDU Subject: keep the first time record

Dear SAS-Ls: Another question is... I want to keep the first time(the first day) transaction record of the same client.

For example below:

obs Client_No funding_date Acct_nbr 1 A2208xxxxx 2002/10/29 0103xxxA 2 A2208xxxxx 2002/10/29 0103xxxB 3 A2208xxxxx 2004/6/9 0103xxxC

In my requirement, i want to keep obs 1 and 2. Any suggestions are highly appreciated!! :) ----------------------------------------- Email messages cannot be guaranteed to be secure or error-free as transmitted information can be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The Centers for Medicare & Medicaid Services therefore does not accept liability for any error or omissions in the contents of this message, which arise as a result of email transmission.

CONFIDENTIALITY NOTICE: This communication, including any attachments, may contain confidential information and is intended only for the individual or entity to which it is addressed. Any review, dissemination, or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message.


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