Dynamic comparison of indicators at different reporting dates based on metadata table

Tanguy — Sun, 15 May 2016 08:52:51 GMT

Dear SAS Community,

I am using enterprise guide to calculate yearly, bi-annual, quarterly, weekly evolution of indicators. I need to make the step of programming and make it dynamic based on a meta data table. I do not know how to proceed. If someone would heve some advice I would be greatful

Here is an example of metadata table and input data.

DATA WORK.METADATA;
    LENGTH
        Indicator_Name   $ 4
        'Evolution calculation basis'n $ 3
        'Indicator availability'n $ 9
        Category         $ 9
        TableID          $ 6 ;
    FORMAT
        Indicator_Name   $CHAR4.
        'Evolution calculation basis'n $CHAR3.
        'Indicator availability'n $CHAR9.
        Category         $CHAR9.
        TableID          $CHAR6. ;
 infile datalines delimiter=','; 
    INPUT
        Indicator_Name   : $CHAR4.
        'Evolution calculation basis'n : $CHAR3.
        'Indicator availability'n : $CHAR9.
        Category         : $CHAR9.
        TableID          : $CHAR6. ;

   DATALINES;
IND1,QoQ,Quarterly,Category1,Table1
IND1,YoY,Quarterly,Category1,Table1
IND2,QoQ,Quarterly,Category1,Table1
IND3,SoS,Bi-annual,Category2,Table1
IND4,YoY,Quarterly,Category3,Table1
IND5,WoW,Weekly   ,Category4,Table2
IND6,YoY,Quarterly,Category4,Table2
IND7,YoY,Yearly   ,Category5,Table2
;
RUN;


DATA INPUT_DATA;
    LENGTH
        CTRY             $ 5
        idnum            $ 3
        date               8
        IND1               8
        IND2               8
        IND3               8
        IND4               8
        IND5               8
        IND6               8
        IND7               8 ;
    FORMAT
        CTRY             $CHAR5.
        idnum            $CHAR3.
        date             DATE9.
        IND1             PERCENT9.2
        IND2             COMMAX32.
        IND3             PERCENT6.
        IND4             COMMA12.
        IND5             COMMAX32.
        IND6             PERCENT9.2
        IND7             COMMAX32. ;

infile datalines delimiter=','; 
INPUT
        CTRY             : $CHAR5.
        idnum            : $CHAR3.
        date             : date9.
        IND1             :  PERCENT9.
        IND2             : COMMA32.
        IND3             :  PERCENT9.
        IND4             : BEST32.
        IND5             : COMMA12.
        IND6             : PERCENT9.
        IND7             : COMMA12. ;


		Datalines;
CTRY1,ID1,31-Dec-09,0.34%,15432456666,41% ,103,3231223,-2.00%,12322212243 
CTRY1,ID1,30-Sep-10,0.30%,12345967512,    ,   ,       ,-3.00%,            
CTRY1,ID1,31-Dec-10,0.10%,12345374512,54% ,102,       ,51.00%,11341917945 
CTRY1,ID1,31-Mar-11,     ,           ,    ,   ,1298289,-3.00%,            
CTRY1,ID1,28-Apr-11,	 ,			 ,    ,   ,1435765,      ,       	  
CTRY1,ID1,31-Mar-11,2.30%,			 ,	  ,	  ,       ,-3.00%,            
CTRY1,ID1,30-Jun-11,5.00%,12349067116,200%,   ,       ,      , 			  
CTRY1,ID1,30-Sep-11,0.07%,12350913418, 	  ,   ,       ,-3.00%,	          
CTRY1,ID1,31-Dec-11,     ,12352759720,33% ,100,       ,30.00%,11349303153 
CTRY1,ID1,31-Mar-12,2.12%,12347220593,	  ,   ,       ,-3.00%,            
CTRY1,ID2,30-Jun-11,5.00%,12358298626,23% ,   ,       ,20.00%,            
CTRY1,ID2,30-Sep-11,0.09%,12360144928,    ,   ,       ,-3.00%,            
CTRY1,ID2,28-Apr-11,	 ,           ,    ,   ,       ,      ,            
CTRY1,ID2,21-Apr-11,0.09%,	         ,    ,   ,1301995,      ,      	  
CTRY2,ID1,31-Dec-09,0.27%,12345965333,33% ,82 ,2584978,-1.60%,9857769794  
CTRY2,ID1,30-Sep-10,0.24%,9876774010 ,    ,   ,       ,-2.40%,            
CTRY2,ID1,31-Dec-10,0.08%,9876299610 ,43% ,82 ,       ,40.80%, 9073534356 
CTRY2,ID1,31-Mar-11,0.00%,		     ,    ,   ,1038631,-2.40%,            
CTRY2,ID1,28-Apr-11,0.00%,			 ,    ,   ,1148612,      ,            
CTRY2,ID1,31-Mar-11,1.84%,           ,    ,   ,       ,-2.40%,            
CTRY2,ID1,30-Jun-11,4.00%,9879253693 ,160%,   ,       ,                   
CTRY2,ID1,30-Sep-11,0.06%,9880730734 ,    ,   ,       ,-2.40%,            
CTRY2,ID1,31-Dec-11,0.00%,9882207776 ,26% ,80 , 	  ,24.00%,9079442522  
CTRY2,ID1,31-Mar-12,1.70%,9877776474 , 	  ,   ,       ,-2.40%,            
CTRY2,ID2,30-Jun-11,4.00%,9886638901 ,18% ,   ,       ,16.00%,            
CTRY2,ID2,30-Sep-11,0.07%,9888115942 , 	  ,   ,       ,-2.40%,            
CTRY2,ID2,28-Apr-11,0.00%,	         ,    ,   ,       ,0.00% ,            
CTRY2,ID2,21-Apr-11,0.07%,		     ,    ,   ,041596 ,0.00% ,            
RUN;

Metadata description:

For each indicator, an 'Evolution calculation basis' is defined

Evolution calculation basis:
- YoY: year to year. the purpose is to calculated the evolution of the indicator between the latest end of year (e.g. 31DEC2011) where the indicator is non missing and the previous end of year. The formula is (B-A)/A. If the indicator is missing at previous end of year, the the program returns NA
- SoS: Semester to Semester: the purpose is to calculated the evolution of the indicator between the latest end of semester (31DECxxxx or 30JUNxxxx) where the indicator is non missing and the previous semester, respectively (30JUNxxxx or 31DECxxxx). The formula is (B-A)/A. If the indicator is missing at previous end of Semester, the program returns NA
- QoQ: quarter to quarter: the purpose is to calculated the evolution of the indicator between the latest end of year (31DEC2011) where the indicator is non missing and the previous end of year. The formula is (B-A)/A. If the indicator is missing at previous end of quarter, the program returns NA
- WoW: week to week: the purpose is to calculated the evolution of the indicator between the latest end of week where the indicator is non missing and the previous end of week. THe formula is (B-A)/A. If the indicator is missing at previous end of week, the program returns NA

For each indicator, an 'Indicator availability' is defined. It tells at which frequency the indicator is avaialble.

THe output table is the result of a transpose and the calculation defined above:

Country	idnum	TableID	Category	Indicator_Name	Evolution calculation basis	Latest non missing Value	Date of latest non missing value	Value at previous reference date for calculation	Evolution value: from latest reference date non missing
CTRY1	ID1	Table1	Category1	IND1	QoQ	2.12%	31-Mar-12	NA	NA
CTRY1	ID1	Table1	Category1	IND1	YoY	2.12%	31-Mar-12	2.30%	-7.83%
CTRY1	ID1	Table1	Category1	IND2	QoQ	12,347,220,593	31-Mar-12	12,352,759,720	-0.04%
CTRY1	ID1	Table1	Category2	IND3	SoS	33%	31-Dec-11	200%	-83.50%
CTRY1	ID1	Table1	Category3	IND4	YoY	…	…	…	…
CTRY1	ID1	Table2	Category4	IND5	WoW	1,435,765	28-Apr-11	1,298,289	10.59%
CTRY1	ID1	Table2	Category4	IND6	YoY	…	…	…	…
CTRY1	ID1	Table2	Category5	IND7	YoY	…	…	…	…
CTRY1	ID2	Table1	Category1	IND1	QoQ	…	…	…	…
CTRY1	ID2	Table1	Category1	IND1	YoY	…	…	…	…
CTRY1	ID2	Table1	Category1	IND2	QoQ	…	…	…	…
CTRY1	ID2	Table1	Category2	IND3	SoS	…	…	…	…
CTRY1	ID1	Table1	Category3	IND4	YoY	…	…	…	…
CTRY1	ID2	Table2	Category4	IND5	WoW	…	…	…	…
CTRY1	ID2	Table2	Category4	IND6	YoY	…	…	…	…
CTRY1	ID2	Table2	Category5	IND7	YoY	…	…	…	…
CTRY2	ID1	Table1	Category1	IND1	QoQ	…	…	…	…
CTRY2	ID1	Table1	Category1	IND1	YoY	…	…	…	…
CTRY2	ID1	Table1	Category1	IND2	QoQ	…	…	…	…
CTRY2	ID1	Table1	Category2	IND3	SoS	…	…	…	…
CTRY2	ID1	Table1	Category3	IND4	YoY	…	…	…	…
CTRY2	ID1	Table2	Category4	IND5	WoW	…	…	…	…
CTRY2	ID1	Table2	Category4	IND6	YoY	…	…	…	…
CTRY2	ID1	Table2	Category5	IND7	YoY	…	…	…	…
CTRY2	ID2	Table1	Category1	IND1	QoQ	…	…	…	…
CTRY2	ID2	Table1	Category1	IND1	YoY	…	…	…	…
CTRY2	ID2	Table1	Category1	IND2	QoQ	…	…	…	…
CTRY2	ID2	Table1	Category2	IND3	SoS	…	…	…	…
CTRY2	ID1	Table1	Category3	IND4	YoY	…	…	…	…
CTRY2	ID2	Table2	Category4	IND5	WoW	…	…	…	…
CTRY2	ID2	Table2	Category4	IND6	YoY	…	…	…	…
CTRY2	ID2	Table2	Category5	IND7	YoY	…	…	…	…

Many thanks for your help

Re: Dynamic comparison of indicators at different reporting dates based on metadata table

ChrisNZ — Tue, 31 May 2016 03:42:50 GMT

You're asking people here for a large chunk of their time in order to understand your large data and numerous calculations, and then come up with a solution.

Please invest some of your time to reduce the size of the problem to a small data set and a couple of calculations, that you'll then be able to extrapolate to the full problem.

topic Re: Dynamic comparison of indicators at different reporting dates based on metadata table in SAS Programming

Dynamic comparison of indicators at different reporting dates based on metadata table

Re: Dynamic comparison of indicators at different reporting dates based on metadata table