topic Re: Macro/Program to determine from tables (represent each month) rate of change for each field in SAS Programming

Macro/Program to determine from tables (represent each month) rate of change for each field

khoff — Mon, 09 Oct 2017 19:25:10 GMT

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..

Re: Macro/Program to determine from tables (represent each month) rate of change for each field

andreas_lds — Mon, 09 Oct 2017 20:40:54 GMT

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.

Re: Macro/Program to determine from tables (represent each month) rate of change for each field

ChrisNZ — Mon, 09 Oct 2017 22:06:07 GMT

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.

Re: Macro/Program to determine from tables (represent each month) rate of change for each field

ChrisNZ — Tue, 10 Oct 2017 01:59:28 GMT

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;

Re: Macro/Program to determine from tables (represent each month) rate of change for each field

khoff — Tue, 10 Oct 2017 13:50:37 GMT

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

Re: Macro/Program to determine from tables (represent each month) rate of change for each field

khoff — Tue, 10 Oct 2017 13:51:31 GMT

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!