BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sufiya
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ghosh
Barite | Level 11

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;

 

Untitled.png

View solution in original post

9 REPLIES 9
ed_sas_member
Meteorite | Level 14

Hi @sufiya 

Could you please supply raw data?

best,

sufiya
Quartz | Level 8

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

ghosh
Barite | Level 11
unable to download this file. Please try saving in text or csv format
sufiya
Quartz | Level 8

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

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

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;

 Untitled.png

sufiya
Quartz | Level 8

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;

 

 

 

sufiya_0-1590757639895.png

 

 

ghosh
Barite | Level 11

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;

 

Untitled.png

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 798 views
  • 1 like
  • 3 in conversation