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
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
You're asking people here for a large chunk of their time in order to understand your large data and numerous calculations, and then come up with a solution.
Please invest some of your time to reduce the size of the problem to a small data set and a couple of calculations, that you'll then be able to extrapolate to the full problem.
You're asking people here for a large chunk of their time in order to understand your large data and numerous calculations, and then come up with a solution.
Please invest some of your time to reduce the size of the problem to a small data set and a couple of calculations, that you'll then be able to extrapolate to the full problem.
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.