BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
khoff
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

5 REPLIES 5
andreas_lds
Jade | Level 19

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.

khoff
Fluorite | Level 6

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

ChrisNZ
Tourmaline | Level 20

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.

 

ChrisNZ
Tourmaline | Level 20

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;

 

 

khoff
Fluorite | Level 6

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! 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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