Proc Report make 2 rows align into 1 row and create a total column

[sufiya]

Hello,

Hoping if someone can please help me and let me know how to make 2 rows align into 1 row and create a total column? 

Thank you in advance !!!

this is what I have :

			19-May-20	20-May-20	21-May-20	22-May-20
Color	Code	Model
Total	Total	Total	16	10	11	21
Red	AAB	Ceiling Fan	1	2	.	5
Red	CCA	Air Conditioner	15	7	11	15
Blue	CCA	Air Conditioner	.	1	.	1

proc report data=Summary_Vol  nowd;

column color Code Model  Day , Volume;

define color  / group 'color' ;

define Code / group 'Code';

define Model / group 'Volume';

define Day  / across '';

define Volume / analysis sum '';

COMPUTE segment;

IF color NE ' ' then hold=color ;

IF color EQ ' ' then color=hold ;

ENDCOMP;

rbreak before / summarize;

compute before;

if color = "" then color = 'Total';

if Code = "" then Code = 'Total';

if Model = "" then Model = 'Total';

ENDCOMP;

run;

This is what I want to get:

Color	Code	Model	19-May-20	20-May-20	21-May-20	22-May-20	Total
Total	Total	Total	16	10	11	21	58
Red	AAB	Ceiling Fan	1	2	.	5	8
Red	CCA	Air Conditioner	15	7	11	15	48
Blue	CCA	Air Conditioner	.	1	.	1	2

[ed_sas_member]

Hi @sufiya 

Could you please supply raw data?

best,

[sufiya]

Hi @ed_sas_member  & @ghosh  - attached the raw data- thank you both! 

[ghosh]

unable to download this file. Please try saving in text or csv format

[sufiya]

Hi @ghosh  - uploaded csv version - thank you again! 

[ghosh]

It's pretty simple, you have done most of the work. Can you provide the data so I could test it out?

[ghosh]

proc report data=work.import nowd spanrows;
column color Code  Model Volume, Day
			  (Volume=tot);
define color  / group order=data;
define Code / group ;
define Model / group ;
define Day  / across ' ';
define Volume / analysis sum '' ;

define tot / analysis sum 'Total'  f=6.0 ;

rbreak before / summarize;
compute before;
 color='Sum';
 endcomp;
run;

 

[sufiya]

Hi @ghosh 

Thank you  - it worked! I  have a question, I want to have the first column 'Color' variables repeat but they don't  - how can I update the query for this?

was adding to get column variables to repeat:

COMPUTE color;
IF color NE ' ' then hold=color ;
IF color EQ ' ' then color=hold ;
ENDCOMP;

full code:

proc report data=WORK.RawData nowd spanrows;
column color Code Model Volume, Day
(Volume=tot);
define color / group order=data;
define Code / group ;
define Model / group ;
define Day / across ' ';
define Volume / analysis sum '' ;

define tot / analysis sum 'Total' f=6.0 ;
COMPUTE color;
IF color NE ' ' then hold=color ;
IF color EQ ' ' then color=hold ;
ENDCOMP;

rbreak before / summarize;
compute before;
color='Sum';
endcomp;


run;

ODS HTML CLOSE;
quit;

[ghosh · Accepted solution]

You almost had it.  Here you go!

proc report data=WORK.import nowd ;
column color Code Model Volume, Day
(Volume=tot);
define color / group order=data;
define Code / group ;
define Model / group ;
define Day / across ' ';
define Volume / analysis sum '' ;

define tot / analysis sum 'Total' f=6.0 ;
COMPUTE color;
IF not missing(color) then hold=color ;
IF missing(color) then color=hold ;
ENDCOMP;

rbreak before / summarize;
compute before;
color='Sum';
endcomp;

run;

[sufiya]

thank you!