/*** TIP 00387 ***/

 /*** Author: Charles Patridge                 ***/
 /***         The Hartford                     ***/
 /***         Corporate Actuarial              ***/
 /***         860-673-9278                    ***/
 /***
 I recently took over a SAS Application (approximately 25 SAS programs) which is 
very linear in design.  It is a Financial Accounting System with numerous SAS variables 
but not a lot of records.  

In addition, these Financial variables can be used a number of times from the Top of 
the system to the bottom of the system and hence the values can change.  Since I am 
not 100% familiar with all of the Accounting formulas scattered throughout the system, 
I needed to design a method to assist me when I needed to troubleshoot why the values of 
certain Financial variables get changed and result in some unusual results.

So, I thought it would help me determine where to start looking in the entire system if 
I was able to see where in the system the values get changed and have unusual values.  
This would allow me to pinpoint exactly where in the system to start looking for possible 
problems.

So, below is a set of macros that will trace the values of any selected Financial Variable(s)
within this system, show what the values are at the end of each major step, and what 
data set has for the value of each variable.  And since each major data step is associated 
with a unique SAS program, I have found this to be quite useful in troubleshooting this 
application.

Hence, I thought the technique might be useful for others when trying to troubleshoot
systems they are unfamiliar with.  One major assumption of this process is to know the 
order when each major data set is created so that the output can be sorted to follow the 
process flow of the Financial Application

TRACE is the major macro (near the bottom) that calls and utilizes the rest of the supporting 
macros listed below
***/

 %macro allmls;
     /*** generate all MLS if requested ***/
     proc sql noprint ;
        select custseg into : list separated by ' '
          from  baton.commontb ;
     quit ;
     %put &list;
     %let quoted   = %str(%')%sysfunc(tranwrd(%sysfunc(compbl(&list)),%str( ),%str(' ')))%str(%');
     %let custseg  = %unquote(& quoted);
     %put &custseg;
  %mend allmls;

 %macro getvar( checkstr );
  /*** getvar macro will check to see what variables exist within each sas dataset ***/
  %let checkstr = %CMPRES( &checkstr );
 data _null_;
  numwords = 1 + length( trim(left("&checkstr"))) - length(trim(left(compress("&checkstr"))));
  call symput( 'numwords', put( numwords,2.));
 run;

 data _null_;
  length vexist $ 200. chkvar $ 32 ;
  dsid = open( "&saslib..&sasdsn", "i" );
  if dsid = 0 then return ;
  do i = 1 to &numwords;
   varno = 0;
   chkvar = scan( "&checkstr", i, " " );
   if chkvar = " " then leave ;
   varno = varnum( dsid, trim(chkvar) );
   if varno ne 0 then vexist = trim(vexist) || " " || chkvar ;
  end;
  rc = close ( dsid ) ;
  call symput( 'vexist', trim(vexist) );
 run;
 %put "vexist = &vexist";
%mend getvar;
 
%macro getdsn( seq ,  sasdsn );
/*** get actual SAS dataset records if SAS variable exists within it ***/
  %getvar( &variable );

  %put "&vexist" &sasdsn ;

  %if "&vexist" ne " " %then %do;
      data &sasdsn;
       length source $14.;
       set baton.&sasdsn( keep=custseg caldate &vexist
                          where = ( custseg in ( &custseg ) ) ) ;
       &myif  ;
       source = "&seq - &sasdsn";
      run;
  %end;
%mend getdsn;

/*** Trace macro ***/
 /*** trace.sas                                                                            ***/
 /*** first set of parameters is a list of Management Lines - each within quotes           ***/
 /*** second set of parameters is list of GAAP Data Elements you wish to see values for 
             - without quotes                                                              ***/
 /*** third parameter is a simple IF statement if needed to select certain records from 
            each dataset in the GAAP processing                                            ***/
 /*** this program allows you to check any ML, any Data Elements, and simple IF statements ***/
 /*** to select where each Data element is first created or input to the GAAP processing   ***/
 /*** it then picks off your selections and creates an excel spreadsheet to troubleshoot   ***/
 /*** if a SAS variable has missing values, it implies the SAS variable(s) does not exist  ***/
 /*** at that point of the system.                                                         ***/

 /*** Another point to make is, as a SAS variable is created, it is carried to the bottom  ***/
 /*** of the system.  So, the number of records usually remains constant but the number of ***/
 /*** Financial SAS variables will grow as the system progresses towards to the end.       ***/

 %global saslib sasdsn vexist checkstr;
 %let saslib = baton;

%macro trace( custseg, variable, myif  );

 %let custseg  = %upcase( &custseg  );
 %let variable = %upcase( &variable );

 %if "ALL" = &CUSTSEG %THEN %DO;
     %allmls ;
 %end;

/*** list of SAS datasets to be checked for variables ***/
 proc datasets library=work nolist;
      delete
      noncpm  cpm     horizon cpmhis
      uw01015a uw01089a
      uw0010a is0010a cf0010a bs0010a im0010a
              is0020a cf0020a bs0020a im0020a bs0030a
      ;
 quit;

 %getdsn( 01, noncpm  );
 %getdsn( 02, cpm     );
 %getdsn( 03, horizon );
 %getdsn( 04, cpmhis  );
 %getdsn( 05, uw01015a);
 %getdsn( 06, uw01089a);
 %getdsn( 07, uw0010a );
 %getdsn( 08, is0010a );
 %getdsn( 09, cf0010a );
 %getdsn( 10, bs0010a );
 %getdsn( 11, im0010a );
 %getdsn( 12, im0020a );
 %getdsn( 13, is0020a );
 %getdsn( 14, cf0020a );
 %getdsn( 15, bs0020a );
 %getdsn( 16, bs0030a );

 data combine;
  set
  %if %sysfunc(exist(work.noncpm )) %then %do;
    noncpm
  %end;
  %if %sysfunc(exist(work.cpm    )) %then %do;
    cpm
  %end;
  %if %sysfunc(exist(work.horizon)) %then %do;
    horizon
  %end;
  %if %sysfunc(exist(work.cpmhis )) %then %do;
    cpmhis
  %end;
  %if %sysfunc(exist(work.uw01015a)) %then %do;
    uw01015a
  %end;
  %if %sysfunc(exist(work.uw01089a)) %then %do;
    uw01089a
  %end;
  %if %sysfunc(exist(work.uw0010a)) %then %do;
    uw0010a
  %end;
  %if %sysfunc(exist(work.is0010a)) %then %do;
    is0010a
  %end;
  %if %sysfunc(exist(work.cf0010a)) %then %do;
    cf0010a
  %end;
  %if %sysfunc(exist(work.bs0010a)) %then %do;
    bs0010a
  %end;
  %if %sysfunc(exist(work.im0010a)) %then %do;
    im0010a
  %end;
  %if %sysfunc(exist(work.is0020a)) %then %do;
    is0020a
  %end;
  %if %sysfunc(exist(work.cf0020a)) %then %do;
    cf0020a
  %end;
  %if %sysfunc(exist(work.bs0020a)) %then %do;
    bs0020a
  %end;
  %if %sysfunc(exist(work.im0020a)) %then %do;
    im0020a
  %end;
  %if %sysfunc(exist(work.bs0030a)) %then %do;
    bs0030a
  %end;
  ;
 run;

 proc sort data=combine out=combine; by source custseg caldate; run;

 proc datasets library=work nolist;
      delete
      noncpm  cpm     horizon cpmhis
      uw01015a uw01089a
      uw0010a is0010a cf0010a bs0010a im0010a
              is0020a cf0020a bs0020a im0020a bs0030a
      ;
 quit;

 proc transpose data=combine out=tranpose;
 by source custseg;
 id caldate;
 format caldate monyy7.;
 var &variable ;
 run;

 proc sort data=tranpose out=tranpose; by source _name_ custseg; run;

 /*** output to an excel spreadsheet ***/
 %myexcel( work.tranpose, "trace.csv", Y );

 %mend trace;

/*** sample calls to TRACE ***/
 ***%trace( "097"             , I_04 C_05_A I_04_G , if '01jul2003'd le caldate le '01sep2003'd );
 ***%trace( "001" "002" "003" , I_01               , if '01jan2001'd le caldate le '01dec2003'd );
 ***%trace( "ALL"             , B_22  B_22_J       , if '01jul2003'd le caldate le '01dec2003'd );
 ***%trace( "001"             , B_22  B_22_J       , if '01jul2003'd le caldate le '01dec2003'd );

/*** called used for example ***/
    %trace( "037" "097"       , I_01 I_01_W01      , if '01jul2003'd le caldate le '01sep2003'd );

/*** Output from the above example ***/
'SOURCE  	  'CUSTSEG  		'_NAME_  	'_LABEL_  		'JUL2003  	'AUG2003  	'SEP2003   
'SOURCE  	  'Customer 		'NAME OF 	'LABEL OF 		'JUL2003  	'AUG2003  	'SEP2003   
	   Segment  		FORMER  	FORMER 
				VARIABLE  VARIABLE  	
 01 - noncpm	037	 	I_01	 Net Written Premium	     	. 	     	. 	        .  
 01 - noncpm	097	 	I_01	 Net Written Premium	     	. 	     	. 	        .  
 01 - noncpm	037	 	I_01_W01	Direct Written Premium	      	. 	     	. 	        .  
 01 - noncpm	097	 	I_01_W01	Direct Written Premium		0 	    	0		0
 02 - cpm		037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 02 - cpm		097	 	I_01	 Net Written Premium		0		0		0
 02 - cpm		037	 	I_01_W01 Direct Written Premium	      	. 	        . 	        .  
 02 - cpm		097	 	I_01_W01 Direct Written Premium	      	. 	        . 	        .  
 05 - uw01015a	097	 	I_01	 Net Written Premium	      	. 	        . 	        .  
 05 - uw01015a	097	 	I_01_W01	Direct Written Premium		0		0		0
 07 - uw0010a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 07 - uw0010a	097	 	I_01	 Net Written Premium		0		0		0
 07 - uw0010a	037	 	I_01_W01	Direct Written Premium		0		0		0
 07 - uw0010a	097	 	I_01_W01	Direct Written Premium		0		0		0
 08 - is0010a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 08 - is0010a	097	 	I_01	 Net Written Premium		0		0		0
 08 - is0010a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 08 - is0010a	097	 	I_01_W01	Direct Written Premium		0		0		0
 09 - cf0010a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 09 - cf0010a	097	 	I_01	 Net Written Premium		0		0		0
 09 - cf0010a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 09 - cf0010a	097	 	I_01_W01	Direct Written Premium		0		0		0
 10 - bs0010a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 10 - bs0010a	097	 	I_01	 Net Written Premium		0		0		0
 10 - bs0010a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 10 - bs0010a	097	 	I_01_W01	Direct Written Premium		0		0		0
 11 - im0010a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 11 - im0010a	097	 	I_01	 Net Written Premium		0		0		0
 11 - im0010a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 11 - im0010a	097	 	I_01_W01	Direct Written Premium		0		0		0
 12 - im0020a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 12 - im0020a	097	 	I_01	 Net Written Premium		0		0		0
 12 - im0020a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 12 - im0020a	097	 	I_01_W01	Direct Written Premium		0		0		0
 13 - is0020a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 13 - is0020a	097	 	I_01	 Net Written Premium		0		0		0
 13 - is0020a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 13 - is0020a	097	 	I_01_W01	Direct Written Premium		0		0		0
 14 - cf0020a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 14 - cf0020a	097	 	I_01	 Net Written Premium		0		0		0
 14 - cf0020a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 14 - cf0020a	097	 	I_01_W01	Direct Written Premium		0		0		0
 15 - bs0020a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 15 - bs0020a	097	 	I_01	 Net Written Premium		0		0		0
 15 - bs0020a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 15 - bs0020a	097	 	I_01_W01	Direct Written Premium		0		0		0
 16 - bs0030a	037	 	I_01	 Net Written Premium	-10480.36105	-6415.593455	-8710.563914
 16 - bs0030a	097	 	I_01	 Net Written Premium		0		0		0
 16 - bs0030a	037	 	I_01_W01	Direct Written Premium	-12524.50856	-8459.74096	-10707.18142
 16 - bs0030a	097	 	I_01_W01	Direct Written Premium		0		0		0