From Monthly to Quarter - Summing amounts & transforming counts

[Nikos]

Hi all,

I have a dataset that looks like this:

CV_ID	TRNS_MONTH	SUM_MONTH	Cnt_ID_MONTH
100	200101	1,500	1
100	200102	0	1
100	200103	1,400	1
100	200104	2,300	1
100	200105	1,100	0
200	200306	360	1
200	200307	500	1
200	200308	700	1
200	200309	0	1
200	200310	450	1
200	200311	0	0

I want to have:

CV_ID	TRNS_QTR	SUM_QTR	Cnt_ID_QTR
100	2001Q1	2,900	1
100	2001Q2	3,400	0
200	2003Q2	360	1
200	2003Q3	1,200	1
200	2003Q4	450	0

I believe that Proc sql would be helpful.

Any hints / suggestions would be very much apprecuiated

Best regards

Nikosly -

[ballardw]

What is the rule for assining values to your Cnt_ID_QTR?

I would make a SAS data varialbe to use the power of formats.

Data want;

     set have;

     date = input(trans_month,yymmn6);

run;

proc summary data=want;

     class cv_id date;

     var sum_month;

     format date yyq6.;

     output out=summary sum(sum_month)=sum_qtr);

run;

For example. Other procs such as tabulate or report would work as well.

[Linlin · Accepted solution]

how about:

data have;

infile cards ;

informat trns_month yymmn6. sum_month comma.;

format trns_month yymmn6.;

input CV_ID TRNS_MONTH SUM_MONTH Cnt_ID_MONTH;

cards;

100 200101 1,500 1

100 200102 0 1

100 200103 1,400 1

100 200104 2,300 1

100 200105 1,100 0

200 200306 360 1

200 200307 500 1

200 200308 700 1

200 200309 0 1

200 200310 450 1

200 200311 0 0

;

proc sql;

  create table want as

    select cv_id, cats(year(TRNS_MONTH),qtr(trns_month)) as trns_qtr length=8,sum(sum_month) as sum_qtr,min(cnt_id_month) as cnt_id_qtr

      from have

         group by cv_id,trns_qtr;

quit;

proc print;run;

                   Obs    CV_ID    trns_qtr    sum_qtr   cnt_id_qtr

                    1      100      20011        2900        1

                    2      100      20012        3400        0

                    3      200      20032         360        1

                    4      200      20033        1200        1

                    5      200      20034         450        0

Linlin

[PGStats]

Slight variation on Linlin's query (a little simpler and giving the date as a numeric representing the first day of the quarter) :

proc sql;

create table want as

     select cv_id,

          intnx("QTR", trns_month, 0) as trns_qtr format=yyq6.,

          sum(sum_month) as sum_qtr format=comma10.,

          min(cnt_id_month) as cnt_id_qtr

     from have

     group by cv_id, trns_qtr;

select * from want;

quit;

PG

[ballardw]

I have a sneaking suspicion that the trns_month variable is text since the example shows it left justified.