Can we create a report as same as PIVOT table in excel

[saswiki]

Hi Experts, 

I have a scenario where i have to replicate the PIVOT TABLE in SAS. Here is the pivot table 

Overall Summary	Count of Invoice Lines	Count of Unique Invoices
100% GR completed	154	24
Part line GR completed	154	24
Not found in Invoice table	48	48
Not found in Invoice table	48	48
Partial GR done	3314	563
GR data not available	1976	395
GR qty greater than Invoice qty	468	57
IBD data not available	570	91
Part line GR completed	281	12
Partial GR done	19	8
Grand Total	3516	635

I have used PROC report and i got the below output: The values are same but i need to list the grief summary inside Overall summary. 

proc report data=combine_all
colwidth=10
spacing=5
headline headskip;

column overall_summary grief_summary count_inv count_inv_uniq;
define overall_summary / group 'Overall Summary';
define grief_summary / group 'Partline Summary';

break after overall_summary /ol
summarize
suppress;

run;

[PaigeMiller]

The values are same but i need to list the grief summary inside Overall summary. 

Show us what the desired output should look like. A screen capture from Excel where you put things into rows and columns the way you want to see it would be helpful.

[saswiki]

Thanks for your time, I have already added the pivot table in the initial, i need to report the same way

[PaigeMiller]

Please provide the data for SAS data set COMBINE_ALL. We cannot program from data provided as screen captures, we cannot program from data in Excel. Please provide the data as WORKING data step code, which you can type in yourself, or you can follow these instructions.

[saswiki]

Hi, 

Here is the dummy program and the dataset

data test;
infile datalines delimiter=','; ;
length overall_summary grief_summary $50 ;
input overall_summary $ grief_summary $ count_inv count_inv_unq ;
datalines ;
100% GR completed Part line, GR completed, 154, 24,
Not found in Invoice table, Not found in Invoice table, 48, 48
Partial GR done, GR data not available, 2011, 567
Partial GR done, GR qty greater than Invoice qty, 468,88
Partial GR done, IBD data not available, 570,89
Partial GR done, Part line GR completed, 281,90
Partial GR done, Partial GR done, 19,10
;
run;

the output i am expecting is attached below. Overall summary will have both group of Overall summary as well as Grief Summary

Overall_Summary	count_inv	count_uniq_inv
100% GR completed	154	24
Part line GR completed	154	24
Not found in Invoice table	48	48
Not found in Invoice table	48	48
Partial GR done	3314	563
GR data not available	1976	395
GR qty greater than Invoice qty	468	57
IBD data not available	570	91
Part line GR completed	281	12
Partial GR done	19	8
Grand Total	3516	635

[saswiki]

Here is the correct program, i have added one comma incorrecty.

data test;
infile datalines delimiter=','; ;
length overall_summary grief_summary $50 ;
input overall_summary $ grief_summary $ count_inv count_inv_unq ;
datalines ;
100% GR completed, Part line GR completed, 154, 24,
Not found in Invoice table, Not found in Invoice table, 48, 48
Partial GR done, GR data not available, 2011, 567
Partial GR done, GR qty greater than Invoice qty, 468,88
Partial GR done, IBD data not available, 570,89
Partial GR done, Part line GR completed, 281,90
Partial GR done, Partial GR done, 19,10
;
run;

[PaigeMiller · Accepted solution]

Thanks. I think it would be difficult to get this exact output from PROC REPORT directly from the data set you provide. I think your have to re-arrange the data first, and then simply use PROC REPORT more as a PROC PRINT with some rows in bold. So I will take a crack at this when I have time, but first let me ask — does the output have to be EXACTLY in the format you show, or are reasonable modifications to what you show acceptable? Those reasonable modifications would make the programming much simpler, and still provide the same results.

[ballardw]

Is there a data set used to make that example data? That appears as if it may be a summary. There a times that desired report layouts are better done with raw data and not summarized values.

---

@saswiki wrote:

Hi, 

Here is the dummy program and the dataset

data test;
infile datalines delimiter=','; ;
length overall_summary grief_summary $50 ;
input overall_summary $ grief_summary $ count_inv count_inv_unq ;
datalines ;
100% GR completed Part line, GR completed, 154, 24,
Not found in Invoice table, Not found in Invoice table, 48, 48
Partial GR done, GR data not available, 2011, 567
Partial GR done, GR qty greater than Invoice qty, 468,88
Partial GR done, IBD data not available, 570,89
Partial GR done, Part line GR completed, 281,90
Partial GR done, Partial GR done, 19,10
;
run;

the output i am expecting is attached below. Overall summary will have both group of Overall summary as well as Grief Summary

Overall_Summary	count_inv	count_uniq_inv
100% GR completed	154	24
Part line GR completed	154	24
Not found in Invoice table	48	48
Not found in Invoice table	48	48
Partial GR done	3314	563
GR data not available	1976	395
GR qty greater than Invoice qty	468	57
IBD data not available	570	91
Part line GR completed	281	12
Partial GR done	19	8
Grand Total	3516	635

---