Dear All,
I hope you will be able to help me in getting the desired result. I am currently working on a specific requirement to generate the below report:
However, when I tried to generate the same, I could able to generate the report in the below format:
STATUS TOTAL 14M08 14M09 14M10 14M11 14M12 15M01 15M02 15M03
-------------------------------------------------------------------------
CLOSE 199 12 10 15 11 8 9 19 10
OPEN 583 46 42 33 61 50 32 34 65 ……. Till the Aug-15.
Now, I got stuck while addressing the below requirements:
I tried several options but unable to get any idea to move forward. My knowledge to SAS is limited and I am in the process of enhancing it.
The sample input data file look like this:
OPEN_ MNTH_
Obs CLOSE_ST MNTH_YM CNT_OPCL
1 OPEN 14M08 1
2 OPEN 14M08 1
3 OPEN 14M09 1
4 OPEN 14M09 1
5 OPEN 14M10 1
6 OPEN 14M10 1
7 CLOSE 14M08 1
8 CLOSE 14M08 1
9 CLOSE 14M09 1
10 CLOSE 14M09 1
11 CLOSE 14M10 1
12 CLOSE 14M10 1
Please help me in this regards to be able to generate the report.
Regards,
Prasad.
Ou. That make problem a bit more complicated . I think it is hard for proc report and proc tabulate , and you need some data step help.
data have; input Obs CLOSE_ST $ OPEN_MNTH_YM $ MNTH_CNT_OPCL; date=input(compress(OPEN_MNTH_YM,,'kd'),yymmn.); cards; 1 OPEN 14M08 2 2 OPEN 14M08 1 3 OPEN 14M09 1 4 OPEN 14M09 1 5 OPEN 14M10 1 6 OPEN 14M10 1 7 CLOSE 14M08 1 8 CLOSE 14M08 1 9 CLOSE 14M09 1 10 CLOSE 14M09 1 11 CLOSE 14M10 1 12 CLOSE 14M10 1 ; run; proc format; picture fmt other='%b-%0y' (datatype=date); run; proc summary data=have nway; class date CLOSE_ST; format date fmt8.; var MNTH_CNT_OPCL; output out=temp(drop=_type_ _freq_) sum=; run; data temp1; set temp; by date; if CLOSE_ST='OPEN' then group=1; else if CLOSE_ST='CLOSE' then group=2; output; dif=dif(MNTH_CNT_OPCL); if last.date then do; CLOSE_ST='Net';MNTH_CNT_OPCL=dif;group=3;output; end; drop dif; run; proc sort data=temp1;by group;run; proc tabulate data=temp1 order=data; class CLOSE_ST date; var MNTH_CNT_OPCL; table CLOSE_ST=' ',(date=' ' all='Total')*MNTH_CNT_OPCL=' '*sum=' '*f=best8.; run;
消息编辑者为:xia keshan
消息编辑者为:xia keshan
data have;
input Obs CLOSE_ST $ OPEN_MNTH_YM $ MNTH_CNT_OPCL;
date=input(compress(OPEN_MNTH_YM,,'kd'),yymmn.);
format date monyy.;
cards;
1 OPEN 14M08 1
2 OPEN 14M08 1
3 OPEN 14M09 1
4 OPEN 14M09 1
5 OPEN 14M10 1
6 OPEN 14M10 1
7 CLOSE 14M08 1
8 CLOSE 14M08 1
9 CLOSE 14M09 1
10 CLOSE 14M09 1
11 CLOSE 14M10 1
12 CLOSE 14M10 1
;
run;
proc tabulate data=have;
class CLOSE_ST date;
format date monyy.;
var MNTH_CNT_OPCL;
table CLOSE_ST=' ',(date=' ' all='Total')*MNTH_CNT_OPCL=' '*sum=' '*f=best8.;
run;
Hi Xia..Thank you so much for your quick response. I do get the desired result, except the net differences at the end as shown in the report.
I am not sure whether I can use COMPUTE statement or something else.
Regards,
Prasad.
Ou. That make problem a bit more complicated . I think it is hard for proc report and proc tabulate , and you need some data step help.
data have; input Obs CLOSE_ST $ OPEN_MNTH_YM $ MNTH_CNT_OPCL; date=input(compress(OPEN_MNTH_YM,,'kd'),yymmn.); cards; 1 OPEN 14M08 2 2 OPEN 14M08 1 3 OPEN 14M09 1 4 OPEN 14M09 1 5 OPEN 14M10 1 6 OPEN 14M10 1 7 CLOSE 14M08 1 8 CLOSE 14M08 1 9 CLOSE 14M09 1 10 CLOSE 14M09 1 11 CLOSE 14M10 1 12 CLOSE 14M10 1 ; run; proc format; picture fmt other='%b-%0y' (datatype=date); run; proc summary data=have nway; class date CLOSE_ST; format date fmt8.; var MNTH_CNT_OPCL; output out=temp(drop=_type_ _freq_) sum=; run; data temp1; set temp; by date; if CLOSE_ST='OPEN' then group=1; else if CLOSE_ST='CLOSE' then group=2; output; dif=dif(MNTH_CNT_OPCL); if last.date then do; CLOSE_ST='Net';MNTH_CNT_OPCL=dif;group=3;output; end; drop dif; run; proc sort data=temp1;by group;run; proc tabulate data=temp1 order=data; class CLOSE_ST date; var MNTH_CNT_OPCL; table CLOSE_ST=' ',(date=' ' all='Total')*MNTH_CNT_OPCL=' '*sum=' '*f=best8.; run;
消息编辑者为:xia keshan
消息编辑者为:xia keshan
Thank you very much XIA. This worked exactly the way I was expecting. Very much appreciated for your help.
Prasad.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.