topic Re: Data cleaning on date and number in New SAS User

Data cleaning on date and number

miss2223 — Mon, 12 Sep 2022 23:28:06 GMT

I have got two questions, below is my data code.

data input;
input Account Name$ birthday: ddmmyy10. startdate: ddmmyy10. enddate: ddmmyy10. service_req;

format birthday: ddmmyy10. startdate: ddmmyy10. enddate: ddmmyy10.;
datalines;
1232 Ryan 01/01/1990 07/04/2017 01/03/2022 9356
1232 Ryan 01/01/1990 02/03/2022 27/08/2022 9667
1440 Chris 13/12/1987 11/03/2019 19/03/2022 5112
1440 Chris 11/12/1987 01/04/2022 30/12/9999 7891
1211 May 05/04/1998 13/09/2017 11/08/2022 1209
1265 Sandy 10/12/1988 12/08/2022 01/10/2022 1287
;

It will be great if someone can help me out, as I'm new to SAS. Please break down to one code per question instead of combine the code to answer both question, if its possible.

1. Typo on Chris's birthday, account 1440 for Chris, one entry of the birthday is 13/12/1987, the other one is 11/12/1987. We believe the latest service request provides the correct date of birth

Account	Name	birthday
1232	Ryan	1/01/1990
1440	Chris	11/12/1987
1211	May	5/04/1998
1265	Sandy	10/12/1988

2. I want the latest service request if there is dup under the same ID and same name. In this scenario I want 9667 for Ryan's service request. And 7891 for Chris.

Account	Name	service_req
1232	Ryan	9667
1440	Chris	7891
1211	May	1209
1265	Sandy	1287

Many thanks!

Re: Data cleaning on date and number

SASKiwi — Tue, 13 Sep 2022 00:52:34 GMT

If you have multiple birth dates for the same person how do you know it is always the one with the latest service_req that is correct? If you code such a general rule it will apply to all people.

Re: Data cleaning on date and number

miss2223 — Tue, 13 Sep 2022 00:58:03 GMT

Hi, Yes you are right. What we been told the latest update of the service_req has the correct information, we just presume thats the case. As we are just dealing with the data, it comes from the book. So I guess its fine to apply to all people. Thanks

Re: Data cleaning on date and number

SASKiwi — Tue, 13 Sep 2022 04:51:53 GMT

Here's one way of getting the correct birth date. Would be worth trying on your actual data to see how it goes:

proc sql;
  create table want as
  select A.*
        ,C.birthday as birthday_correct
  from input as A
  left join
  (select Account
         ,max(enddate) as enddate_Max format = ddmmyy10.
   from input
   group by Account
  ) as B
  on A.Account = B.Account
  left join input as C
  on A.account = C.Account
  and B.enddate_Max = C.enddate
  ;
quit;