topic Re: Can we create a report as same as PIVOT table in excel in SAS Enterprise Guide

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

saswiki — Tue, 24 Jan 2023 11:31:09 GMT

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;

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

PaigeMiller — Tue, 24 Jan 2023 12:42:43 GMT

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.

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

saswiki — Tue, 24 Jan 2023 12:49:51 GMT

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

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

PaigeMiller — Tue, 24 Jan 2023 12:57:57 GMT

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.

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

saswiki — Tue, 24 Jan 2023 14:06:29 GMT

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

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

saswiki — Tue, 24 Jan 2023 14:07:16 GMT

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;

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

PaigeMiller — Tue, 24 Jan 2023 15:03:50 GMT

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.

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

ballardw — Tue, 24 Jan 2023 15:16:47 GMT

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