Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Pivoting Data in SAS Visual Analytics

Reply
Occasional Contributor
Posts: 13

Pivoting Data in SAS Visual Analytics

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 

 

teamsizefreq
A2274151266411
B20835468
C385419104928
D17310529880
E274750860149
F161045848
G73772492586

 

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

 

ValuesABCDEFGGrand Total
Sum of size227415208338541917310274750161047377930458
Sum of freq12664115468104928529880860149584824925865265270

 

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

Valued Guide
Posts: 505

Re: Pivoting Data in SAS Visual Analytics

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

SAS Super FREQ
Posts: 384

Re: Pivoting Data in SAS Visual Analytics

Occasional Contributor
Posts: 13

Re: Pivoting Data in SAS Visual Analytics

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.

Frequent Contributor
Posts: 76

Re: Pivoting Data in SAS Visual Analytics

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.

 

Capturetmp.GIF

Post a Question
Discussion Stats
  • 4 replies
  • 167 views
  • 2 likes
  • 4 in conversation