Counting Days in PROC SQL

[MHP]

Hey everyone,

I have been trying to determine the amount of days between the first date and the last date. I have tried subtracting the first date from the last date but have run into a strange issue. If the amount of days between the first and last date is over a 100, it is still listed as 100. So if there was 200 days between the first and last visit, it would be listed as 100. 

Example:

PROC SQL;

CREATE TABLE FIRSTLAST AS

SELECT ID,

            COUNT(ID) AS COUNT,

            MIN(DOS_FROM) AS FIRST,

            MAX(DOS_TO) AS LAST,

            DOS_TO - DOS_FROM AS DAYS

FROM DATA

GROUP BY ID;

QUIT;

Any help would be much appreciated and if anyone has a better suggestion as to how to construct a better syntax, I am all ears.

Thanks,

Mike

[ChrisNZ · Accepted solution]

Like this?

data HAVE;
  do ID = 1 to 5;
    do N=1 to 5;
      DOS_FROM=today()-365;
      DOS_TO=DOS_FROM+int(ranuni(0)*365);
      output;
    end;
  end;
  format DOS: date9.;
run;


proc sql;
  create table WANT as
  select ID
        ,count(ID)                 as COUNT
        ,min(DOS_FROM)             as FIRST format=date9.
        ,max(DOS_TO)               as LAST  format=date9.
        ,max(DOS_TO)-min(DOS_FROM) as DAYS
  from HAVE
  group by ID;
quit;

ID	COUNT	FIRST	LAST	DAYS
1	5	09SEP2015	18JUN2016	283
2	5	09SEP2015	07SEP2016	364
3	5	09SEP2015	20AUG2016	346
4	5	09SEP2015	17AUG2016	343
5	5	09SEP2015	26AUG2016	352

[MHP]

Thank you so much Chris! You are a lifesaver.