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

Hello

In the following proc  report  I want to show also name of day below the date.

(I will delete my previous post because maybe it was not  explained well)

 

 Data RawTbl;
informat date1 date9.;
format date1 date9.;
input ID date1 Y groupp $;
NameDay = put(date1,dowName. -l) ;
cards;
1 '17Jan2019'd 10 a
2 '17Jan2019'd 20 a
3 '19Jan2019'd 30 a
4 '21Jan2019'd 40 b
5 '23Jan2019'd 50 b
6 '18Jan2019'd 60 b
7 '17Jan2019'd 70 a
8 '08Jan2019'd 80 c
9 '04Jan2019'd 90 c
;
Run;

title;
proc report data=RawTbl nowd;
column groupp date1,Y;
define groupp / group;
define date1 / across;/*I want to show also NameDay under date1*/
define Y / SUM;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@Ronein wrote:

SAS1.PNG

 

Hello

This is the required output.

 

 


Don't know about Proc Report. Here is a Proc Tabulate that gets close:

proc tabulate data=RawTbl;
   class groupp date1 nameday;
   format nameday downame.;
   var y;
   table groupp="" all='Total',
         date1=""*nameday=""*y=""*sum=""*f=best5.
         all='Total'*y=""*sum=""*f=best5.
         /box=groupp misstext='0'
         
   ;
run;

This won't work if you want to do manipulations with summarized values such as adding columns though.

 

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

Please post a visual example of the expected output.

Also test your code, the code as posted crashes.

 


@Ronein wrote:

Hello

In the following proc  report  I want to show also name of day below the date.

(I will delete my previous post because maybe it was not  explained well)

 

 Data RawTbl;
informat date1 date9.;
format date1 date9.;
input ID date1 Y groupp $;
NameDay = put(date1,dowName. -l) ;
cards;
1 '17Jan2019'd 10 a
2 '17Jan2019'd 20 a
3 '19Jan2019'd 30 a
4 '21Jan2019'd 40 b
5 '23Jan2019'd 50 b
6 '18Jan2019'd 60 b
7 '17Jan2019'd 70 a
8 '08Jan2019'd 80 c
9 '04Jan2019'd 90 c
;
Run;

title;
proc report data=RawTbl nowd;
column groupp date1,Y;
define groupp / group;
define date1 / across;/*I want to show also NameDay under date1*/
define Y / SUM;
run;


 

Ronein
Meteorite | Level 14

SAS1.PNG

 

Hello

This is the required output.

 

 

ballardw
Super User

@Ronein wrote:

SAS1.PNG

 

Hello

This is the required output.

 

 


Don't know about Proc Report. Here is a Proc Tabulate that gets close:

proc tabulate data=RawTbl;
   class groupp date1 nameday;
   format nameday downame.;
   var y;
   table groupp="" all='Total',
         date1=""*nameday=""*y=""*sum=""*f=best5.
         all='Total'*y=""*sum=""*f=best5.
         /box=groupp misstext='0'
         
   ;
run;

This won't work if you want to do manipulations with summarized values such as adding columns though.

 

Ronein
Meteorite | Level 14

It is great but I would like to know how to do it with proc Report too.

 

Kurt_Bremser
Super User

Maxim 14: Use the Right Tool.

If the suggested proc tabulate gets what you need, there's no need to waste time by beating another tool into submission that's not really suited.

Ronein
Meteorite | Level 14

Thanks,

If the target is to learn then we might know different ways that getting same result.

In this case as I understand it is not recommended to use proc report

Thanks

 

Kurt_Bremser
Super User

The ideal tool for a report like yours is Web Report Studio, where you can use a multidimensional table ("cube") as base and set up your dimensions as wanted; row- and column-summary cells are just a mouseclick away.

ballardw
Super User

@Ronein wrote:

Thanks,

If the target is to learn then we might know different ways that getting same result.

In this case as I understand it is not recommended to use proc report

Thanks

 


Nesting in multiple dimensions is not Proc Report's strength. Proc Report has the ability to use do more complex in-body-of-table calculations and not statistic information with care. Proc Tabulate doesn't do that. But does more complex nesting of class values as well as creating multiple table layouts with a single procedure call.

 

I suspect that if you are using the correct ODS destination and are extremely careful with literal characters you could get similar appearance with a custom picture format for data values that displays the date plus name of week with a split character in the body of the formatted value..

I'll leave that as an exercise for the interested reader.

PaigeMiller
Diamond | Level 26

When I want a report with row statistics at the right and column statistics at the bottom, I use a combination of PROC SUMMARY followed by PROC REPORT. This is very general, if you want means instead of totals, or if you want percents instead of totals, or medians or standard deviations or percentiles, the modifications to PROC SUMMARY should be obvious. It extends easily to weighted sums, weighted means, weighted percents, etc. in the PROC SUMMARY part.

 

 Data RawTbl;
informat date1 date9.;
format date1 date9.;
input ID date1 Y groupp $;
NameDay = weekday(date1);
format nameday downame.;
longname=cats(put(date1,date7.),'~{newline}',put(nameday,downame.));
cards;
1 17Jan2019 10 a
2 17Jan2019 20 a
3 19Jan2019 30 a
4 21Jan2019 40 b
5 23Jan2019 50 b
6 18Jan2019 60 b
7 17Jan2019 70 a
8 08Jan2019 80 c
9 04Jan2019 90 c
;
Run;
proc summary data=rawtbl;
	var y;
	class longname groupp;
	output out=_sums_ sum=;
run;
data _sums_;
	set _sums_;
	if missing(longname) then longname='ZZZZ';
	if missing(groupp) then groupp='zzzz';
run;
proc format;
	value $groupf 'zzzz'='Total';
	value $longf 'ZZZZ'='Total' other=[$30.];
run;		
ods escapechar='~';
options missing='0';
proc report data=_sums_;
    columns ("Group" groupp) longname,y;
	define groupp/group ' ' order=internal format=$groupf.;
	define longname/across order=internal format=$longf. 'Date';
	define y/sum ' ';
run;

Please in your own code adopt the indenting of statements as I have added above underneath your code.

 

Also, there is no need for CARDS; to contain '17JAN2019'd when you can use 17JAN2019 which is easier to type and less prone to typing errors.

 

This method of concatenating date and day of week fails if the data contains more than one month because of the requirement of spelling out month in 3 letter abbreviations. If you can use the YYMMDD10. format, then I think it works across multiple months.

--
Paige Miller
ballardw
Super User

The date literal form of 'ddmonyyyy'd is for use in lines of code not really for reading in as data, especially not with a date9 format.

Your data step likely should be:

Data RawTbl;
informat date1 date9.;
format date1 date9.;
input ID date1 Y groupp $;
NameDay = put(date1,dowName. -l) ;
cards;
1 17Jan2019 10 a
2 17Jan2019 20 a
3 19Jan2019 30 a
4 21Jan2019 40 b
5 23Jan2019 50 b
6 18Jan2019 60 b
7 17Jan2019 70 a
8 08Jan2019 80 c
9 04Jan2019 90 c
;
Run;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1123 views
  • 2 likes
  • 4 in conversation