How to get proc report to format missing values and correctly order dates?

[EC27556]

Hi,

I have some output from proc report that looks like this:

Under 01/03/2021, you can see there is a '.' missing value. Is there any way to format this so it is a 0?

In addition, you can see that the 2020 months are at the end of the table rather than the beginning. Is there any way to change this?

Here is my code:

proc report data=Merged out=a;
	
	column SC1 SC2 SC3 SC4 ap_month,(Probability);
	
	define ap_month / Across;
	define SC1 / Group;
	define SC2 / Group;
	define SC3 / Group;
	define SC4 / Group;
	define Probability / sum;

run;

and the data looks like this:

You can see that in between the highlighted rows there should be another row with 01/03/2021 and this is the root cause of the missing value in the table.

[PaigeMiller]

Under 01/03/2021, you can see there is a '.' missing value. Is there any way to format this so it is a 0?

options missing=0;

or create a custom format.

[EC27556]

Thanks, two questions re. this:

1) if i use options missing=0, is there a way of changing options back to default (where missing=.) other than just running options missing=.;  ?

2) How exactly would I go about using a default format in proc report? I have tried to run

proc format;
	value Probability .=0;
run;


proc report data=Merged out=a;
	
	column SC1 SC2 SC3 SC4 ap_month,(Probability);
	
	define ap_month / Across;
	define SC1 / Group;
	define SC2 / Group;
	define SC3 / Group;
	define SC4 / Group;
	define Probability / sum format=probability.;

run;

but this just makes every value in my output table = 0!

[Tom]

Your format has no default width, so SAS will set the default to 1 character!

Example:

data test;
  input grp yrmon :yymmdd. value ;
  format yrmon yymm7.;
cards;
1 2020-12-01 100
1 2021-01-02 200
2 2020-12-10 3000
2 2021-01-01 .
;

proc format;
  value zeromiss .='0' other=[comma12.2];
run;

proc report data=test;
  column grp value,yrmon ;
  define grp / group;
  define yrmon / across order=internal;
  define value / sum ' ' format=zeromiss.;
run;

[Tom]

Why are you ordering the columns by month/day? (or is that day/month)? instead of the actual date?

If AP_MONTH is a date variable then use ORDER=INTERNAL to order by the date.

If AP_MONTH is a character string then store the dates in the string in Year-Month-Date order instead so they will sort properly (and also to prevent confusing half of your audience).

[EC27556]

Ahh yes sorry, this is dd/mm/yyyy. order=internal has sorted, thanks 🙂

[Cynthia_sas]

Hi:
System options missing=0 will help with the 0. But if you want the dates to be in a different order, you'd have to do something like: sort by ap_month before the PROC REPORT step and then use order=internal on your DEFINE statement.
Something like this:

I see that you're using OUT= with PROC REPORT. With ACROSS items, that is going to cause absolute column numbers to appear in the output dataset. You can deal with them easily, but just something to be aware of. If you change the ORDER= option then that will change what the columns represent.
Cynthia

[EC27556]

Thanks Cynthia,

In terms of resetting Options to default after I have run my proc report, is there a standard way of doing this or would you just write Options Missing=.; to reset?

[Cynthia_sas]

Hi, that's the standard way to do it.
cynthia