Help using Base SAS procedures

From Monthly to Quarter - Summing amounts & transforming counts

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

From Monthly to Quarter - Summing amounts & transforming counts

Hi all,

I have a dataset that looks like this:

CV_IDTRNS_MONTHSUM_MONTHCnt_ID_MONTH
1002001011,5001
10020010201
1002001031,4001
1002001042,3001
1002001051,1000
2002003063601
2002003075001
2002003087001
20020030901
2002003104501
20020031100

I want to have:

CV_IDTRNS_QTRSUM_QTRCnt_ID_QTR
1002001Q12,9001
1002001Q23,4000
2002003Q23601
2002003Q31,2001
2002003Q44500

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

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

View solution in original post


All Replies
Super User
Posts: 11,343

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

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

Respected Advisor
Posts: 4,927

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: 11,343

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 253 views
  • 4 likes
  • 4 in conversation