Help using Base SAS procedures

PROC REPORT summary report help

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

PROC REPORT summary report help

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.


Accepted Solutions
Solution
‎08-27-2015 03:59 AM
Super User
Posts: 10,023

Re: PROC REPORT summary report help

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


All Replies
Super User
Posts: 10,023

Re: PROC REPORT summary report help

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;
Occasional Contributor
Posts: 7

Re: PROC REPORT summary report help

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.

Solution
‎08-27-2015 03:59 AM
Super User
Posts: 10,023

Re: PROC REPORT summary report help

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

Occasional Contributor
Posts: 7

Re: PROC REPORT summary report help

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

Prasad.

🔒 This topic is solved and locked.

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

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