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