topic Re: From Monthly to Quarter - Summing amounts & transforming counts in SAS Procedures

From Monthly to Quarter - Summing amounts & transforming counts

Nikos — Wed, 20 Jun 2012 14:55:57 GMT

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 -

Re: From Monthly to Quarter - Summing amounts & transforming counts

ballardw — Wed, 20 Jun 2012 20:34:29 GMT

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.

Re: From Monthly to Quarter - Summing amounts & transforming counts

Linlin — Wed, 20 Jun 2012 22:10:16 GMT

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

Re: From Monthly to Quarter - Summing amounts & transforming counts

PGStats — Thu, 21 Jun 2012 02:28:02 GMT

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;

Re: From Monthly to Quarter - Summing amounts & transforming counts

ballardw — Thu, 21 Jun 2012 16:30:39 GMT

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