Transposition

[strugglingwsas]

Hello,

I am trying to do a short time trend analysis for multiple categories over time. My data is not sorted properly within the excel and it is not possible to do so manually. Is there a way to do the time trend analysis as the given screenshot using the sample data (also given)? I really appreciate the help! This should answer all my questions!

[Kurt_Bremser · Accepted solution]

See this for starters:

proc import
  datafile="/folders/myfolders/sample_data_1.xlsx"
  out=have
  dbms=xlsx
  replace
;
run;

/* create a transposed dataset */
proc transpose data=have out=trans (drop=_label_ rename=(_name_=stat)) prefix=year;
var total:;
id year;
run;

/* create a transposed report */
proc tabulate data=have;
var total:;
class year;
table total:,year;
run;

/* create a multi-line plot */
proc sgplot data=have;
x2axis max=1700;
xaxis max=45;
vline year / response = total_people;
vline year / response = total_shops;
vline year / response = total_schools;
vline year / response = total_trees y2axis;
run;

[strugglingwsas]

Hello,

This worked in terms of the chart! When I try to make the graph, it labels the left and right sides of the graph (y-axis) as total_people (on the left) and total_trees (on the right). Is there a way to put: a graph title and add correct labels just on the vertical and horizontal axes? I really appreciate your help!

[Kurt_Bremser]

Maxim 1: Read the Documentation.

You can add options to the AXIS statements, and use a TITLE:

title "My Graph";
/* create a multi-line plot */
proc sgplot data=have;
xaxis label="Years";
yaxis max=45 label="People, Shops and Schools";
y2axis max=1700 label="Trees";
vline year / response = total_people;
vline year / response = total_shops;
vline year / response = total_schools;
vline year / response = total_trees y2axis;
run;