Group by in Sql with date range format

[Ccasagran737]

Hi,

I have this:

data have;
     infile datalines delimiter=','; 
      attrib gbv length  = 8
               date length = 8 format = date9. informat =ddmmyy10.;
       input gbv date ;
       datalines;
       1000, 01/01/2017
        23,    10/03/2016
        344,  23/06/2014
        44,    04/11/2010
        445,  01/04/2013
        63,    21/06/2015
        7,      01/01/2018
        83,    01/09/2017
        932,  17/12/2016
       10,     05/02/2019
      ;
run;

I create this new format

proc format;
      value calendar
                "01JAN1970"d - "31DEC2013"d = "G) ANTE 2013"
                "01JAN2014"d - "31DEC2014"d = "F) 2014"
                "01JAN2015"d - "31DEC2015"d = "E) 2015"
                "01JAN2016"d - "31DEC2016"d = "D) 2016"
                "01JAN2017"d - "31DEC2017"d = "C) 2017"
                "01JAN2018"d - "31MAR2018"d = "B) 1Q 2018"
                "01APR2018"d - "31DEC2030"d = "A) POST 2018";
run;

And I want an output like this one that I've obtained with proc means but using sql.

proc means data = have noprint nway missing;
     format date calendar.;
     class date;
     var gbv;
     output out  = want (drop = _type_ _freq_) sum=;
run;

proc sort;
     by descending date;
run;

proc print data = want;
run;

Unluckily my proc sql dosen't work properly, I would like to have a single row for each range.

proc sql;
     select date format calendar., 
                sum(gbv) as gbv format commax20.
     from have
     group by date
     order by date desc;
quit;

Do you know why? Could you help me to fix it?

Thanks a lot for your help!

Daniele

[Ccasagran737]

Hi!

I'm trying to create new format for summarize data in a proc sql step.

This is my data

			  
data have;
  infile datalines delimiter=','; 
  attrib gbv length  = 8
         date length = 8 format = date9. informat =ddmmyy10.;
  input gbv date ;
	datalines;
	1000, 01/01/2017
	23,   10/03/2016
	344,  23/06/2014
	44,   04/11/2010
	445,  01/04/2013
	63,   21/06/2015
	7,    01/01/2018
	83,   01/09/2017
	932,  17/12/2016
	10,   05/02/2019
	;
run;

I create this new format

proc format;
	value calendar
	"01JAN1970"d - "31DEC2013"d = "G) ANTE 2013"
        "01JAN2014"d - "31DEC2014"d = "F) 2014"
	"01JAN2015"d - "31DEC2015"d = "E) 2015"
	"01JAN2016"d - "31DEC2016"d = "D) 2016"
	"01JAN2017"d - "31DEC2017"d = "C) 2017"
	"01JAN2018"d - "31MAR2018"d = "B) 1Q 2018"
	"01APR2018"d - "31DEC2030"d = "A) POST 2018";
run;

I want an output like this one that I've obtained with a proc means step but using a proc sql.

proc means data = have noprint nway missing;
	format date calendar.;
	class date;
	var gbv;
	output out  = want (drop = _type_ _freq_) sum=;
run;
proc sort;
	by descending date;
run;

proc print data = want;
run;

Unluckily my proc sql doesn't work properly and in my output the dates are not group by in unique row

proc sql;
	select date format calendar., 
	           sum(gbv) as gbv format commax20.
	from have
	group by date
	order by date desc;
quit;

Do you know why and how I can fix my program?

Thanks for your help!

Daniele

[PeterClemmensen · Accepted solution]

In your current SQL Procedure, you group by the actual dates. Not the formatted dates. You can make a small correction that makes this possible

proc sql;
     select put(date, calendar.) as date,
                sum(gbv) as gbv format commax20.
     from have
     group by calculated date
     order by date desc;
quit;

[Ccasagran737]

Great!!! thank for your help!

[PeterClemmensen]

Anytime 🙂