DATA Step, Macro, Functions and more

Difference in terms of days between dates in same column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Difference in terms of days between dates in same column

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


Accepted Solutions
Solution
‎03-15-2017 10:29 AM
PROC Star
Posts: 7,365

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

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

View solution in original post


All Replies
PROC Star
Posts: 290

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

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

Occasional Contributor
Posts: 16

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

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

Valued Guide
Posts: 797

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

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;

  1. Read in all the records for a single id, while updating MINDOB and MAXDOB for that ID
  2. Calculates DIFF
  3. Re-reads all the records for the same id, and outputs the record, now with correct DIFF
Solution
‎03-15-2017 10:29 AM
PROC Star
Posts: 7,365

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

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

Occasional Contributor
Posts: 16

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

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.

 

PROC Star
Posts: 7,365

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

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

Occasional Contributor
Posts: 16

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

Thanks sir for the help. I missed the Id part in first solution.Thanks a lot for help Smiley Happy
Valued Guide
Posts: 797

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

@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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 188 views
  • 4 likes
  • 4 in conversation