Comparing/transpose observations at different reference dates based on a metadata table

[Tanguy]

Dear SAS community,

The purpose is to calculate dynamically yearly, quarterly, monthly, weekly evolution of indicators based on areference/metadata table (see code attached to generate an example of meta data table and input data). I used enterprise guide to do it, but it is not dynamic. I need to make the step of coding but I am quite beginner.

An example of metadata table and input data is defined below:. 

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;

• Description of the metadata field 'Evolution calculation basis': 
  • QoQ = Quarter to Quarter. Evolution between the latest end of quarter where data is non missing  and the quarter before. If data at the quarter before is missing, the program returns 'NA'. The evolution has the following formula (B-A/A)
  • YoY = Year to year. Evolution between the latest end of year (31DECxxxx) where data is non missing  and the year before. If data at the year before is missing, the program returns 'NA'. The evolution has the following formula (B-A/A)
  • SoS = Semester to semester. Evolution between the latest end of [end of December; end of June] where data is non missing  and 6 month before - respectively [end of June; end of December]. If data at the Semester before is missing, the program returns 'NA'.. The evolution has the following formula (B-A/A)
  • WoW. Week to week. Evolution between the latest end of week (e.g. 28APR2011) where data is non missing  and the week before. If data at the week before is missing, the program returns 'NA'. The evolution has the following formula (B-A/A)

• Description of the metadata field 'Indicator availability': tells at which frequency the indicator is available.

The result table should have the following format.

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

In advance, thanks a lot for your help.

Kind regards

[Ksharp]

Plz don't post data in that form , Nobody would like to edit data for you. Better SAS code. It sounds you want create sas code dynamically according to that Meta Dataset. Use data step to build it and %include it. Where is the last column (Evolution value) coming from ? How do you get it ? YoY means a year ? QoQ means a Quarter ? SoS means double years ? WoW means a week ?

[Tanguy]

Dear XIa,

THanks for the tips, I am quite new here.

I have added the code for the generation of the metadata table and the input data, as well as the description of the metadata table.

Thanks a lot for your help