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 -
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
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.
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
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
I have a sneaking suspicion that the trns_month variable is text since the example shows it left justified.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.