topic Counting Days in PROC SQL in SAS Programming

Counting Days in PROC SQL

MHP — Wed, 07 Sep 2016 23:23:53 GMT

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

Re: Counting Days in PROC SQL

ChrisNZ — Wed, 07 Sep 2016 23:41:41 GMT

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

Re: Counting Days in PROC SQL

MHP — Wed, 07 Sep 2016 23:46:54 GMT

Thank you so much Chris! You are a lifesaver.