Create Report with 2 way dimensions

[Ronein]

Hello

I want to create  a report that display the data  but in a different culture.

I want to create report with different structure of the data
There will be 2 categorical variables in X-Axis :1-Amnt 2-PCT
There will be 2 categorical variables in Y-Axis :1-Field 2-Factor
Total rows in the report: 7
Total columns in the report:6
Total cells with data in the report:42

Data tbl;
 INFILE DATALINES DLM=',';
input field $ PCT Amnt   Factor	Reduce ;
cards;
max,.,.,1,-4490
max,30,20000,1,-2461
max,40,20000,1,-2981
max,50,20000,1,-3433
max,30,50000,1,-3621
max,40,50000,1,-3843
max,50,50000,1,-4039
max,.,.,1.1,-4157
max,30,20000,1.1,-2306
max,40,20000,1.1,-2788
max,50,20000,1.1,-3202
max,30,50000,1.1,-3361
max,40,50000,1.1,-3568
max,50,50000,1.1,-3749
max,.,.,1.2,-3858
max,30,20000,1.2,-2161
max,40,20000,1.2,-2607
max,50,20000,1.2,-2988
max,30,50000,1.2,-3125
max,40,50000,1.2,-3319
max,50,50000,1.2,-3486
S_Max,.,.,1,-5114
S_Max,30,20000,1,-2697
S_Max,40,20000,1,-3281
S_Max,50,20000,1,-3802
S_Max,30,50000,1,-4094
S_Max,40,50000,1,-4342
S_Max,50,50000,1,-4564
S_Max,.,.,1.1,-4804
S_Max,30,20000,1.1,-2573
S_Max,40,20000,1.1,-3122
S_Max,50,20000,1.1,-3606
S_Max,30,50000,1.1,-3859
S_Max,40,50000,1.1,-4093
S_Max,50,50000,1.1,-4301
S_Max,.,.,1.2,-4514
S_Max,30,20000,1.2,-2449
S_Max,40,20000,1.2,-2964
S_Max,50,20000,1.2,-3415
S_Max,30,50000,1.2,-3637
S_Max,40,50000,1.2,-3857
S_Max,50,50000,1.2,-4052
;
run;

/*I want to create report with different structure of the data*/
/*There will be 2 categorical variables in X-Axis :1-Amnt 2-PCT*/
/*There will be 2 categorical variables in Y-Axis :1-Field 2-Factor*/
/*Total rows in the report: 7*/
/*Total columns in the report:6*/
/*Total cells with data in the report:42*/
 

[ed_sas_member · Accepted solution]

Hi @Ronein 

PROC TABULATE can be a great tool to do this job.

It is not very clear what you actually want to get so please feel free to adapt the following code as follows:

- add the missing option in the CLASS statement if you consider missing values in categories as valide values

- which data do you want to display at the end according to the different categories: the mean of reduce for example? -> specify the variable to analyze in the VAR statement if it is a numeric one, and specify the statistic to compute in the TABLES statement (-> where I have specified "mean".

- If you want to display the categories of PCT for each category of AMNT -> put an * as specified in the below code in the TABLES Statement. Otherwise, remove it. The same for Field and Factor.

Hope this help,

Best,

proc tabulate data=tbl;
	class Amnt PCT field Factor / missing;
	var Reduce;
	tables (Amnt*PCT),(field*Factor)*(Reduce*mean) ;
run;

[Ronein]

Thank you

Here you use Mean calculation but I just need to display the current data and not calculate it.

As you can see in the row data there are 42 cells with numeric data and in the desired result there will be also 42 cells with numeric data

[ed_sas_member]

Hi @Ronein 

So you can remove 'mean' as there is only one possibility (-> NB: it is actually strictly equivalent, as the mean will be based on only one value)

[ed_sas_member]

Hi @Ronein 

The PROC REPORT proposed by @PaigeMiller would also work.

You can add the missing option in the first option like below to consider missing values as valid values:

proc report data=tbl missing;
    columns amnt pct field,factor,reduce;   
    define amnt/group;  
    define pct/group;
    define field/across;
    define factor/across;
    define reduce/analysis sum;
run;

[PaigeMiller]

---

@Ronein wrote:

Here you use Mean calculation but I just need to display the current data and not calculate it.

---

The mean of a single value is that value. You have to tell SAS what to do with that single value, this is how you get that value into the table from PROC TABULATE or PROC REPORT.

[PaigeMiller]

PROC REPORT can create reports with the layout you want

UNTESTED CODE

proc report data=tbl;
    columns amnt pct field,factor,reduce;
    define amnt/group;
    define pct/group;
    define field/across;
    define factor/across;
    define reduce/analysis sum;
run;