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 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.
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;
Hello
This is the required output.
@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.
It is great but I would like to know how to do it with proc Report too.
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.
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
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.
@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.
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.
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.