I created a library for this next project I am working on, and within it there will be 12 tables--one representing each month. The goal is to see month-to-month the rate of change for each field. I have researched, and tried a few things and have had no luck. I am hoping to have it as an easily accessible/exportable finalize report. I visualized having the table names (months) as the rows, and the column headers as the name of each field (they will be the same month to month). I know I can use proc tabulate for this, but I am struggling with how to represent the change factor for each field from month to month. The field data is mostly qualitative data I am new to the board, so please let me know if more info is needed. Any ideas? Thank you in advance, any help is greatly appreciated! Macro preferred, any help appreciated!
Idea 1:
Fields
Months
Field 1 Field 2 etc.....
Jan to Feb 100% 80%
Feb to Mar 0% 50%
Mar to Apr 20% 100%
etc..
Idea 2:
Fields
Months
Field 1 Field 2 etc.....
Jan to Feb Changed Not changed
Feb to Mar Not Changed Not Changed
Mar to Apr Changed Changed
etc..
Since there is no further reply, this example may get you started:
data HAVE;
set SASHELP.CITIDAY;
where DATE < '25jan1988'd;
PCTDIFF_SNYDJCM =SNYDJCM /lag(SNYDJCM )-1;
PCTDIFF_SNYSECM =SNYSECM /lag(SNYSECM )-1;
PCTDIFF_DSIUSWIL=DSIUSWIL/lag(DSIUSWIL)-1;
PCTDIFF_DFXWCAN =DFXWCAN /lag(DFXWCAN )-1;
PCTDIFF_DFXWUK90=DFXWUK90/lag(DFXWUK90)-1;
PCTDIFF_DSIUKAS =DSIUKAS /lag(DSIUKAS )-1;
PCTDIFF_DSIJPND =DSIJPND /lag(DSIJPND )-1;
PCTDIFF_DCD1M =DCD1M /lag(DCD1M )-1;
PCTDIFF_DTBD3M =DTBD3M /lag(DTBD3M )-1;
if DATE > '04jan1988'd;
format PCTDIFF: percent.;
run;
proc transpose out=TRANS;
var PCTDIFF:;
by DATE;
run;
ods graphics on / height=800px width=600px;
proc sgpanel data=TRANS;
panelby _NAME_ / novarname columns=1 rows=9 headerattrs=(weight=Bold color=cx000088) spacing=10;
vbar DATE / response=COL1;
rowaxis label=' ';
colaxis label=' ';
run;
proc tabulate data=TRANS;
class DATE _NAME_;
var COL1;
table DATE=' ', _NAME_=' '*COL1=' '*sum=' '*format=percent.;
run;
Difficult to suggest something without seeing data.
I tend to give percentages a try, but with some minor modification: instead of 75% i would write -25% - seems to be easier to read. The main difference between your first and second idea is a fomat cloaking the real values.
Thank you for the reply, much appreciated! I am going to give the report a try below and see where it gets me. I cannot share any of the data sets as it is sensitive data with personal information
The coding is the easiest part. You first need to design the report you want.
Otherwise you'll spend your time time writing never ending versions of unsatisfactory reports.
A well-designed report is where an analyst brings value.
Since you'll have 100+ numbers in a table (12 months times ?? variables), that's probably a level-2 report.
For a high level grasp of the data, you could first have a graph to show all these figures. Then people can go see the details they want.
Since there is no further reply, this example may get you started:
data HAVE;
set SASHELP.CITIDAY;
where DATE < '25jan1988'd;
PCTDIFF_SNYDJCM =SNYDJCM /lag(SNYDJCM )-1;
PCTDIFF_SNYSECM =SNYSECM /lag(SNYSECM )-1;
PCTDIFF_DSIUSWIL=DSIUSWIL/lag(DSIUSWIL)-1;
PCTDIFF_DFXWCAN =DFXWCAN /lag(DFXWCAN )-1;
PCTDIFF_DFXWUK90=DFXWUK90/lag(DFXWUK90)-1;
PCTDIFF_DSIUKAS =DSIUKAS /lag(DSIUKAS )-1;
PCTDIFF_DSIJPND =DSIJPND /lag(DSIJPND )-1;
PCTDIFF_DCD1M =DCD1M /lag(DCD1M )-1;
PCTDIFF_DTBD3M =DTBD3M /lag(DTBD3M )-1;
if DATE > '04jan1988'd;
format PCTDIFF: percent.;
run;
proc transpose out=TRANS;
var PCTDIFF:;
by DATE;
run;
ods graphics on / height=800px width=600px;
proc sgpanel data=TRANS;
panelby _NAME_ / novarname columns=1 rows=9 headerattrs=(weight=Bold color=cx000088) spacing=10;
vbar DATE / response=COL1;
rowaxis label=' ';
colaxis label=' ';
run;
proc tabulate data=TRANS;
class DATE _NAME_;
var COL1;
table DATE=' ', _NAME_=' '*COL1=' '*sum=' '*format=percent.;
run;
Thank you Proc Star! Appreciate the feedback, and the time! I am going to give this a try with the data set I think that is the best approach!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.