How do I add multiple categorical variables in SAS VA line charts

[nadaelmo]

Hi Everyone, 

I have an example SAS dataset with the following structure:

date	0	1	2
Jan-14	2.3	3.1	4.2
Feb-14	4	5.5	4.5
Mar-14	5.6	6.9	6.5
Apr-14	7.1	7.5	7.9
May14	8.9	8.4	8.3
Jun-14	9.5	9.9	9.3
Jul-14	10.7	11.6	11.9
Aug-14	11.4	12.6	11.6
Sep-14	12.2	13.5	15
Oct-14	12.9	13.9	16.2
Nov-14	13.7	14.7	17.9
Dec-14	14.9	15.4	18.6
Jan-15	20	21	23.5

As of right now I have the data graphed on excel with the 0, 1, 2 variables on the x-axis and their corresponding percent values on the y-axis. Essentially the graph shows the growth of vars 0, 1, and 2 across time. My Question is how do I replicate this graph in SAS Visual analytics? I tried the line chart option in VA but that doesn't allow you to add multiple categorical variables. 

[Sam_SAS]

Hello,

The way that you visualized this data in Excel is unconventional, is there a particular industry context where it is common to present it this way?

Usually you would present this data either as bar chart or a line chart with multiple response variables:

To replicate your Excel chart, I think it would be necessary to restructure the data to be more like this:

date	Variable	Value
Jan14	0	2.3
Jan14	1	3.1
Jan14	2	4.2

Then you could assign "Variable" as the category role and Date as the group role.

I restructured the data for the first two dates and it looks like this:

Sam

[Panagiotis]

I agree with @Sam_SAS . The original visualization was definitely unconventional. If you need it the way you have it, you can do that. However if you can, I'd use some of Sam's suggestions.

- Peter  

[Panagiotis]

I believe you will need to transpose the data. Something like this:

date	type	Measure
14-Jan	0	2.3
14-Jan	1	3.1
14-Jan	2	4.2
14-Feb	0	4
14-Feb	1	5.5
14-Feb	2	4.5

Once your data is transposed. Use the Date column as the group, the Type column as the category, and the Measure column as the measure.

Here is the code I used to create the fake table, transpose it, then promote it to the CAS server. I did it quickly so please modify and check it:

cas conn;

libname casuser cas caslib = 'casuser';

data casuser.test;
    input date:$10. one:8. two:8. three:8.;
    infile datalines dsd missover;
    datalines;
Jan-14,2.3, 3.1, 4.2
Feb-14 , 4,   5.5, 4.5
Mar-14 , 5.6, 6.9, 6.5
Apr-14  ,7.1, 7.5, 7.9
May14   ,8.9, 8.4, 8.3
Jun-14  ,9.5, 9.9, 9.3
Jul-14  ,10.7,11.6,11.9
Aug-14  ,11.4,12.6,11.6
Sep-14  ,12.2,13.5,15
Oct-14  ,12.9,13.9,16.2
Nov-14  ,13.7,14.7,17.9
Dec-14  ,14.9,15.4,18.6
Jan-15  ,20,21,23.5
;
run;

* Tranpose*;
proc transpose data=casuser.test 
               out=test2(rename=(col1=Measure _name_=group));
    by Date;
    var one two three;
run;

* load into CAS and promote*;
proc casutil;
    load data=work.test2 
    casout='transposed' outcaslib='casuser' 
    promote;
quit;

By default the transpose of columns one, two and three gets the label NAME OF FORMER VARIABLE. You can change that in the code or in Visual Analytics. I left it as is. Here is a screenshot of the line chart in Visual Analytics:

- Peter