topic Re: Displaying data range in proc sql summary in SAS Programming

Displaying data range in proc sql summary

devsas — Thu, 26 Oct 2017 18:57:59 GMT

Good Afternoon everyone! So, I just finished summarizing a report and got everything tied together well, except displaying the date range. I have multiple records for same id's with different dates- all 1st day of the month-in the format 01/01/2014, 02/01/2014, 08/01/2017 and so on. I also have premiums etc which I need to sum for all records for the same id. What I need is to create another variable displaying the date range/span for the same id.

data have;


input id premium date mmddyy10. ;

format date mmddyy10. ;
cards;

1 10 01/01/2014
1 10 02/01/2014
1 10 04/01/2014
2 15 01/01/2015
2 15 02/01/2015
2 15 04/01/2015
;
run;

data want;
length date_range $35 ;
input id totalpremium date_range $ ;
cards;
1 30 01/01/2014-04/01/2014
2 45 01/01/2015-04/01/2015
;
run;

What I have tried so far-

Proc sql;


create table want as select id, sum(premium) as total premium, min(date)||max(date) as date_range from have group by id;


quit;

Thanks in advance!

Re: Displaying data range in proc sql summary

art297 — Thu, 26 Oct 2017 19:07:49 GMT

You're close! Try:

proc sql;
  create table want as
    select id, sum(premium) as total_premium,
    catx('-',put(min(date),mmddyy10.),put(max(date),mmddyy10.)) as date_range
      from have
        group by id
  ;


quit;

Art, CEO, AnalystFinder.com

Re: Displaying data range in proc sql summary

devsas — Wed, 01 Nov 2017 21:28:30 GMT

Good evening everyone!

This is little modification of an earlier problem I posted on this thread. As you see in my 'Have' dataset, the months are not continuous sometimes. So, for example, if there is a missing month in consecutive records (jan2014, feb2014, april 2014), I need to break down the range bit differently, so rather than original 'want' data set, my new want data set will look like-

data want;
length date_range $35 ;
input id totalpremium date_range $ ;
cards;
1 20 01/01/2014-02/29/2014
1 10  04/01/2014 -04/30/2014
2 30 01/01/2015-02/28/2015
2 15 04/01/2015 -04/30/2015
;
run;

If there is no break per id, then the range will be from 1st day of 1st month to last day of lastmonth of that record.

Thanks in advance!

Re: Displaying data range in proc sql summary

art297 — Wed, 01 Nov 2017 21:59:06 GMT

This should work but, unlike your example, there were only 28 days in Feb 2014:

data have;
  input id premium date mmddyy10. ;
  format date mmddyy10.;
  cards;
1 10 01/01/2014
1 10 02/01/2014
1 10 04/01/2014
2 15 01/01/2015
2 15 02/01/2015
2 15 04/01/2015
;

data need (drop=last_date);
  set have;
  by id;
  last_date=lag(date);
  if first.id then seq=1;
  else if intck('month',last_date,date,'D') gt 1 then seq+1;
run;

proc sql;
  create table want as
    select id, sum(premium) as total_premium,
    catx('-',put(min(date),mmddyy10.),
             put(intnx('month',max(date),0,'e'),mmddyy10.)) as date_range
      from need
        group by id,seq
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Displaying data range in proc sql summary

devsas — Wed, 01 Nov 2017 23:15:12 GMT

Thanks Sir so much! Really appreciate it. It worked.