Get rid of missing values in PROC REPORT - Help! 1

[khfor2007]

Hi,
I am running the following program and producing the below output.

data p2005;
input fac_id $ year month pd ;
date=mdy(month,01,year);
format date mmddyy10. ;
datalines;
FRE 2005 1 21
FRE 2005 2 12
FRE 2005 3 3
FRE 2005 4 24
FRE 2006 1 14
FRE 2006 2 6
FRE 2006 3 25
OAK 2005 1 20
OAK 2005 2 4
OAK 2005 3 26
OAK 2005 4 27
OAK 2006 1 18
OAK 2006 2 10
OAK 2006 3 6
;
run;

ods listing close;
ods rtf
body='c:\temp\odstest.rtf' ;
proc report data=p2005 nowindows missing headskip;
column fac_id year,month,pd;
where '01Mar2005'd <= date <= '01Mar2006'd ;

define fac_id / group ;
define year / across ' ' ;
define month / across ;
run;

quit;
ods rtf close;
ods listing;

**********************************;
* This is the output ;
**********************************;

2005 2006
month month
1 2 3 4 1 2 3 4
fac_id pd pd pd pd pd pd pd pd
FRE . . 3 24 14 6 25 .
OAK . . 26 27 18 10 6 .


Is there a way in Proc Report that I could get rid of the missing month 1 and month 2 in 2005 and month 4 in 2006
as follows:

2005 2006
month month
3 4 1 2 3
fac_id pd pd pd pd pd
FRE 3 24 14 6 25
OAK 26 27 18 10 6

Any help would be greatly appreciated !

Kyle.

 

[Kosa13]

Maybe something like this:

/**********************************/
data p2005;
input fac_id $ year month pd ;
date=mdy(month,01,year);
format date mmddyy10. ;
datalines;
FRE 2005 1 21
FRE 2005 2 12
FRE 2005 3 3
FRE 2005 4 24
FRE 2006 1 14
FRE 2006 2 6
FRE 2006 3 25
OAK 2005 1 20
OAK 2005 2 4
OAK 2005 3 26
OAK 2005 4 27
OAK 2006 1 18
OAK 2006 2 10
OAK 2006 3 6
;
run;

Data X;
Set p2005;
Format DATA $8.;

Where '01Mar2005'd <= date <= '01Mar2006'd ;

DATA=compress(month||"_"||year);
Run;

Proc Sort Data=x;
by fac_id;
Run;

proc transpose data=X out=Y (drop=_name_);
var pd;
id DATA;
by fac_id;
run;

ods listing close;
ods rtf
body='c:\temp\odstest.rtf' ;

Data _null_;
Set Y;
File print Ods;
Put _ods_;
Run;


quit;
ods rtf close;
ods listing;

/**********************************/

Now You can use Proc Template to editing columns (if You want)...

 

[khfor2007]

Thanks for the tips Kosa13.

I wonder if we could get rid of the missing values still using PROC REPORT. My intention is the use ODS with the PROC REPORT, and avoid using PROC TEMPLATE.

 

[sarav1981]

Hi Khfor2007,

You can remove the display of a report item which has a missing or zero value using the NOZERO option in the define statement of the PROC REPORT.

Alter your PROC REPORT statement by adding this option on the item PD as shown below.

proc report data=p2005 nowindows missing headskip;
column fac_id year,month,pd;
where '01Mar2005'd <= date <= '01Mar2006'd;

define fac_id / group;
define year   / across ' ' ;
define month  / across ;
define pd  / nozero ;

run;

Hope this helps!

Sarav

 

[Kosa13]

Interest option

But how do this when we specify "OUT" file ?

/****************************************************/
proc report data=p2005 Out=X (Drop=_Break_) nowindows missing headskip;
column fac_id year,month,pd;
where '01Mar2005'd <= date <= '01Mar2006'd;

define fac_id / group;
define year / across ' ' ;
define month / across ;
define pd / nozero ;
run;
/****************************************************/