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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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


Accepted Solutions
Solution
‎10-10-2017 09:49 AM
Super User
Posts: 2,495

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

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;

 

 

View solution in original post


All Replies
Valued Guide
Posts: 624

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

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.

New Contributor
Posts: 3

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

Posted in reply to andreas_lds

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

Super User
Posts: 2,495

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

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.

 

Solution
‎10-10-2017 09:49 AM
Super User
Posts: 2,495

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

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;

 

 

New Contributor
Posts: 3

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

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! 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 143 views
  • 3 likes
  • 3 in conversation