DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 20

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

[ Edited ]

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.

 

 

CountryidnumTableIDCategoryIndicator_NameEvolution calculation basisLatest non missing Value Date of latest non missing valueValue at previous reference date for calculationEvolution value: from latest reference date non missing
CTRY1ID1Table1Category1IND1QoQ2.12%31-Mar-12NANA
CTRY1ID1Table1Category1IND1YoY2.12%31-Mar-122.30%-7.83%
CTRY1ID1Table1Category1IND2QoQ                      12,347,220,59331-Mar-12                      12,352,759,720-0.04%
CTRY1ID1Table1Category2IND3SoS33%31-Dec-11200%-83.50%
CTRY1ID1Table1Category3IND4YoY
CTRY1ID1Table2Category4IND5WoW                                 1,435,76528-Apr-11                                 1,298,28910.59%
CTRY1ID1Table2Category4IND6YoY
CTRY1ID1Table2Category5IND7YoY
CTRY1ID2Table1Category1IND1QoQ
CTRY1ID2Table1Category1IND1YoY
CTRY1ID2Table1Category1IND2QoQ
CTRY1ID2Table1Category2IND3SoS
CTRY1ID1Table1Category3IND4YoY
CTRY1ID2Table2Category4IND5WoW
CTRY1ID2Table2Category4IND6YoY
CTRY1ID2Table2Category5IND7YoY
CTRY2ID1Table1Category1IND1QoQ
CTRY2ID1Table1Category1IND1YoY
CTRY2ID1Table1Category1IND2QoQ
CTRY2ID1Table1Category2IND3SoS
CTRY2ID1Table1Category3IND4YoY
CTRY2ID1Table2Category4IND5WoW
CTRY2ID1Table2Category4IND6YoY
CTRY2ID1Table2Category5IND7YoY
CTRY2ID2Table1Category1IND1QoQ
CTRY2ID2Table1Category1IND1YoY
CTRY2ID2Table1Category1IND2QoQ
CTRY2ID2Table1Category2IND3SoS
CTRY2ID1Table1Category3IND4YoY
CTRY2ID2Table2Category4IND5WoW
CTRY2ID2Table2Category4IND6YoY
CTRY2ID2Table2Category5IND7YoY

 

In advance, thanks a lot for your help.

 

Kind regards

 

Super User
Posts: 10,048

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

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 ?
Contributor
Posts: 20

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 203 views
  • 0 likes
  • 2 in conversation