BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
B2SAS
Calcite | Level 5

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:

Capture.PNG

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:

  1. Displaying totals on the right most side.
  2. Displaying the net differences at the end of the report.
  3. Covert the MONTH-YEAR format  YYMM  as 'MMM-YY' .

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

4 REPLIES 4
Ksharp
Super User

Code: Program


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;
B2SAS
Calcite | Level 5

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.

Capture.PNG

I am not sure whether I can use COMPUTE statement or something else.

Regards,

Prasad.

Ksharp
Super User

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

B2SAS
Calcite | Level 5

Thank you very much XIA. This worked exactly the way I was expecting. Very much appreciated for your help.

Prasad.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 995 views
  • 1 like
  • 2 in conversation