topic Difference in terms of days between dates in same column in SAS Programming

Difference in terms of days between dates in same column

Nitish1003 — Tue, 14 Mar 2017 17:21:43 GMT

Hi I am trying to find out difference between dates as per Name.

data test;
INPUT NAME $ DOB;
INFORMAT DOB DATE9.;
FORMAT DOB DATE9.;
DATALINES;
NITISH 10MAR2016
PRIYA 10DEC2016
PRIYA 01JAN2016

SAKSHI 20DEC2016

SAKSHI 20APR2016
SAKSHI 02JAN2016
;
RUN;

So output will be like

NAME DIFF
NITISH (10MAR2016-10MAR2016) i.e. 0 days
PRIYA (10DEC2016-10DEC2016)
SAKSHI (20DEC2016-20APR2016)
SAKSHI (20DEC2016-02JAN2016)
SAKSHI (20APR2016-02JAN2016)

Please help me to reach to solution. Itried using proc sort and appling lag in my dataset but was not able to achieve the results

Re: Difference in terms of days between dates in same column

collinelliot — Tue, 14 Mar 2017 18:20:39 GMT

I don't understand your desired results. Priya, for example, has "PRIYA (10DEC2016-10DEC2016)" but I don't see any result for her second row of data with 01JAN2016.

Shouldn't there be a result for PRIYA 10DEC2016 and 01JAN2016 with a difference of 344 days?

If you can tighten up what you expect to see, it will be easier to come up with a solution.

My apologies if I simply don't understand.

Thanks,

Collin

Re: Difference in terms of days between dates in same column

art297 — Tue, 14 Mar 2017 18:41:37 GMT

I think you're looking for something like:

proc sql;                  
  create table want as    
    select a.*, count(a.name) as count, b.dob as dob2,
           abs(dob2-a.dob) as diff           
      from test a join test b
        on a.name eq b.name
          group by a.name
            order by a.id
  ;              
quit;

Art, CEO, AnalystFinder.com

Re: Difference in terms of days between dates in same column

Nitish1003 — Wed, 15 Mar 2017 01:43:36 GMT

Sorry for the typo the output will be like

NAME DIFF
NITISH (10MAR2016-10MAR2016) i.e. 0 days
PRIYA (10DEC2016-01JAN016)
SAKSHI (20DEC2016-20APR2016)
SAKSHI (20DEC2016-02JAN2016)
SAKSHI (20APR2016-02JAN2016)

This would ne my deisre reult. Please Collin if you check it and let me know how to solve this problem.

Thanks

Nitish

Re: Difference in terms of days between dates in same column

mkeintz — Wed, 15 Mar 2017 02:10:48 GMT

You want to calculate the maximum date range of DOB for each name, and then assign that diff to every record in the data set. If your data test is grouped (but not neccessarily in ascending or descending order) by name, then:


data want (drop=mindob maxdob);
 do until (last.name);
   set test;
   by name notsorted;
   mindob=min(mindob,dob);
   maxdob=max(maxdob,dob);
 end;
 diff=maxdob-mindob;
 do until (last.name);
   set test;
   by name notsorted;
   output;
 end;
run;

This program;

Read in all the records for a single id, while updating MINDOB and MAXDOB for that ID
Calculates DIFF
Re-reads all the records for the same id, and outputs the record, now with correct DIFF

Re: Difference in terms of days between dates in same column

Nitish1003 — Wed, 15 Mar 2017 14:29:26 GMT

Hi Sir

Your solution allmost worked for me but facing one issue.I am getting duplicate data i.e.

Example for Priya

Name DOB count DOB2 diff

Priya 01JAN2016 4 10DEC2016 344

Priya 10DEC2016 4 01JAN2016 344

Is there way to Over come this because i want only one row.

Re: Difference in terms of days between dates in same column

art297 — Wed, 15 Mar 2017 15:07:04 GMT

Is the following what you want?:

data test;
  INPUT NAME $ DOB;
  INFORMAT DOB DATE9.;
  FORMAT DOB DATE9.;
  id=_n_;
  DATALINES;
NITISH 10MAR2016
PRIYA 10DEC2016
PRIYA 01JAN2016
SAKSHI 20DEC2016
SAKSHI 20APR2016
SAKSHI 02JAN2016
;
proc sql;                  
  create table want as    
    select a.*, count(a.name) as count, b.dob as dob2,
           abs(dob2-a.dob) as diff           
      from test a join test b
        on a.name eq b.name
          group by a.name
            having a.id le b.id
              order by a.id
  ;              
quit;

Art, CEO, AnalystFinder.com

Re: Difference in terms of days between dates in same column

mkeintz — Wed, 15 Mar 2017 16:17:13 GMT

@Nitish1003

The reason you saw duplicates in the output of @art297's first response is because that is how you displayed the desired output in your initial post.

Re: Difference in terms of days between dates in same column

Nitish1003 — Wed, 15 Mar 2017 17:32:42 GMT

Thanks sir for the help. I missed the Id part in first solution.Thanks a lot for help 🙂