Solved
Contributor
Posts: 68

# From Monthly to Quarter - Summing amounts & transforming counts

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 -

Accepted Solutions
Solution
‎06-20-2012 06:10 PM
Super Contributor
Posts: 1,636

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

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

All Replies
Super User
Posts: 13,563

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

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.

Solution
‎06-20-2012 06:10 PM
Super Contributor
Posts: 1,636

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

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

Posts: 5,535

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

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

PG
Super User
Posts: 13,563

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

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

🔒 This topic is solved and locked.