topic Re: proc report in SAS Programming

proc report

Ronein — Tue, 21 Jan 2020 11:16:41 GMT

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;

Re: proc report

Kurt_Bremser — Tue, 21 Jan 2020 12:10:23 GMT

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;

Re: proc report

ballardw — Tue, 21 Jan 2020 17:59:31 GMT

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;

Re: proc report

Ronein — Tue, 21 Jan 2020 21:27:45 GMT

Hello

This is the required output.

Re: proc report

ballardw — Tue, 21 Jan 2020 23:49:29 GMT

@Ronein wrote:

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.

Re: proc report

Ronein — Wed, 22 Jan 2020 05:05:37 GMT

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

Re: proc report

Kurt_Bremser — Wed, 22 Jan 2020 08:10:12 GMT

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.

Re: proc report

Ronein — Wed, 22 Jan 2020 08:17:04 GMT

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

Re: proc report

Kurt_Bremser — Wed, 22 Jan 2020 08:33:06 GMT

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.

Re: proc report

ballardw — Wed, 22 Jan 2020 17:58:20 GMT

@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.

Re: proc report

PaigeMiller — Wed, 22 Jan 2020 18:45:24 GMT

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.