How to suppress blank cells when using Proc Report?

[EC27556]

Hi,

Using this code:

proc report data=Merged split='~';
	
	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 
run;

I have been able to create the following table:

As you can see, there are missing cells under the SC Columns. I.e. under scGroupA1 there are blank cells. Is there any way of filling those blank cells? I.e. the blank cells under scGroupA2 would be filled with scGroupA1 and the blank space under scGroupA2 would read scGroupA2 etc.

Thanks 🙂

[PaigeMiller]

I.e. the blank cells under scGroupA2 would be filled with scGroupA1 and the blank space under scGroupA2 would read scGroupA2 etc.

I am really unable to grasp the meaning of this sentence. is it a typographical error?

Please SHOW US what you want, type in somehow the desired output under SC1 SC2 SC3 SC4.

[EC27556]

Sorry was meant to read: "I.e. the blank cells under scGroupA1 would be filled with scGroupA1 and the blank cell under scGroupA2 would read scGroupA2."

So in the leftmost column (SC1) instead of scGroupA1 appearing once, it would appear in the blank cells underneath it too. And then under scGroupA2 the blank cell would also read as scGroupA2.

I want the same thing done in SC2, SC3 and SC4 columns too with the scGroup values there. Is this possible?

[EC27556]

In other words this:

🙂

[Ksharp]

If your data has already GROUP calcualted , try ORDER usage.

define SC1 / Group;
-->
define SC1 / ORDER;

otherwise you need calculated these sum before PROC REPORT.

PROC SQL;
create table Merged as
select SC1 ,SC2 ,SC3, SC4 ,ap_month,sum(Probability) as Probability
from have
group by SC1 ,SC2 ,SC3, SC4 ,ap_month;
quit;

[EC27556]

Hi sorry im not entirely sure what you mean by this. Are you saying that I need to run:

title 'Check_CIS DPM';
proc report data=Merged2 split='~';
	
	column SC1 SC2 SC3 SC4 ap_month,(Probability);
	
	define ap_month / Across;
	define SC1 / Order;
	define SC2 / Order;
	define SC3 / Order;
	define SC4 / Order;
	define Probability / sum;


run;

and if this doesnt work then beforehand i need to run:

PROC SQL;
create table Merged2 as
select SC1 ,SC2 ,SC3, SC4 ,ap_month,sum(Probability) as Probability
from Merged
group by SC1 ,SC2 ,SC3, SC4 ,ap_month;
quit;

and then run again? If so this doesnt seem to work!

[Ksharp]

I mean if your data have been organized like :

SC1 SC2 SC3 SC4 ap_month Probability
a   a1  a2   a3  a4  1
b   b1  b2   b3  b4  2
c   c1  c2   c3  c4  3
d   d1  d2   d3  d4  4

you can use my PROC REPORT.

Otherwise, if your data like:

SC1 SC2 SC3 SC4 ap_month Probability
a   b  c   d  e  1
a   b  c   d  e  2
a   b  c   d  e  3
a   b  c   d  e  4

You need firstly calculated it by PROC SQL ,before PROC REPORT.

[EC27556]

My data is set up like this:

so i think it has already been grouped and doesnt need the proc sql step?

However, if i now run:

title 'Check_CIS DPM';
proc report data=Merged2 split='~';
	
	column SC1 SC2 SC3 SC4 ap_month,(Probability);
	
	define ap_month / Across;
	define SC1 / Order;
	define SC2 / Order;
	define SC3 / Order;
	define SC4 / Order;
	define Probability / sum;


run;

I get:

Is this what you mean I should do?

[Ksharp · Accepted solution]

OK. It is my fault . I did not get data to test my code. So I made some dummy dataset to test it.

data merged;
 set sashelp.heart;
run;

proc report data=Merged split='~' nowd ;
	
	column status status2 sex sex2 bp_status bp_status2 Smoking_Status,(MRW);
	
	define Smoking_Status / Across;
	define status / Group noprint;
	define sex / Group noprint;
	define bp_status / Group noprint;
	define MRW / sum '';

	define status2 / computed;
	define sex2 / computed;
	define bp_status2 / computed;

compute before bp_status;
_status=status;
_sex=sex;
_bp_status=bp_status;
endcomp;

compute status2/character length=40;
status2=_status;
endcomp;
compute sex2/character length=40;
sex2=_sex;
endcomp;
compute bp_status2/character length=40;
bp_status2=_bp_status;
endcomp;
run;

[Ksharp]

[EC27556]

This is perfect, thanks! One other thing, in the very first picture I sent (as part of my original question) you can see that the dates are not in order - the last 2 columns are 01/11/2020 and 01/12/2020. Is there any way of making these the first 2 columns instead? I'm not sure why proc report has ordered this way.

[Ksharp]

You need some real data,So I can test it .

Or you could try this option .

proc report data=Merged split='~';
	
	column SC1 SC2 SC3 SC4 ap_month,(Probability);
	
	define ap_month / Across descending ;
	define SC1 / Group;
	define SC2 / Group;
	define SC3 / Group;
	define SC4 / Group;
	define Probability / sum;
	format 
run;