DATA Step, Macro, Functions and more

Counting Days in PROC SQL

Accepted Solution Solved
Reply
New Contributor MHP
New Contributor
Posts: 3
Accepted Solution

Counting Days in PROC SQL

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


Accepted Solutions
Solution
‎09-07-2016 07:46 PM
PROC Star
Posts: 1,759

Re: Counting Days in PROC SQL

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

View solution in original post


All Replies
Solution
‎09-07-2016 07:46 PM
PROC Star
Posts: 1,759

Re: Counting Days in PROC SQL

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
New Contributor MHP
New Contributor
Posts: 3

Re: Counting Days in PROC SQL

Thank you so much Chris! You are a lifesaver.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 274 views
  • 0 likes
  • 2 in conversation