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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.