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;
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.