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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.