topic Re: Pivoting Data in SAS Visual Analytics in SAS Visual Analytics

Pivoting Data in SAS Visual Analytics

sasadmin_tlove — Wed, 15 Feb 2017 10:43:13 GMT

Good morning all,

One of our business units who want to start using VA for thier BAU reporting have asked a simple question, but i'm struggling to find an way to visualise the data the way they want,

they currently have the orginal dataset looking like

team	size	freq
A	227415	1266411
B	2083	5468
C	385419	104928
D	17310	529880
E	274750	860149
F	16104	5848
G	7377	2492586

and they want to pivot the output on the report to look exactly like

Values	A	B	C	D	E	F	G	Grand Total
Sum of size	227415	2083	385419	17310	274750	16104	7377	930458
Sum of freq	1266411	5468	104928	529880	860149	5848	2492586	5265270

Is there someone who could tell me if this is possible and how to achieve it in Visual Analytics

Re: Pivoting Data in SAS Visual Analytics

rogerjdeangelis — Wed, 15 Feb 2017 14:35:49 GMT

data have;
retain team typ;
input team $ size freq;
typ="sum_of_size";val=size;output;
typ="sum_of_freq";val=freq;output;
cards4;
A 227415 1266411
B 2083 5468
C 385419 104928
D 17310 529880
E 274750 860149
F 16104 5848
G 7377 2492586
;;;;
run;quit;


Ods Exclude All;
Ods Output Observed=want;
Proc Corresp Data=have Observed dim=1;
   Table Typ, Team;
weight val;
Run;
Ods Select All;
proc print data=want;run;


LABEL                A          B          C          D          E          F          G        SUM

sum_of_freq    1266411       5468     104928     529880     860149       5848    2492586    5265270
sum_of_size     227415       2083     385419      17310     274750      16104       7377     930458
Sum            1493826       7551     490347     547190    1134899      21952    2499963    6195728

Re: Pivoting Data in SAS Visual Analytics

Madelyn_SAS — Wed, 15 Feb 2017 14:44:29 GMT

SAS Visual Data Builder has a "Pivot By" feature:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n1q1acd3jwqopsn11hc2p8zo8avv.htm

Re: Pivoting Data in SAS Visual Analytics

sasadmin_tlove — Thu, 16 Feb 2017 08:18:15 GMT

This looks like the right idea, is this in the data prep stage? the designers were asking me from a dashboard point of view, if possible.

Re: Pivoting Data in SAS Visual Analytics

itchyeyeballs — Sat, 18 Feb 2017 11:39:12 GMT

Doesn't the crosstab object give you what you need?

You may need to normalise (unpivot) your data first so that the numeric values are all in the same column with an additional column to identify to get the layout you describe though.