Hello guys,
So basically I have was given this massive dataset with 429300 rows of data. This is my job SAS job and I have no idea where to start.
The dataset consist of 30 months of data, each month consist of 2 sectors plus sum of both sectors (so three values, 1 , 2 and 0 to represent sum of both sectors), each sector consists of 4 sizes + sum of all 4 sizes (so 1,2,3,4 + 0 to represent sum of sizes, 5 values in total), each size consists of 8 states/territories in Australia plus 0 to represent entire country (1,2,3,4,5,6,7,8 + 0, so 9 values).
Each of the states/territories consists of 19 industry divisions and subdivisions.
Each division have different number of subdivisions. There are a total of 86 subdivisions, classified into one of the 19 divisions, so each division will have its number of subdiv + 0 to represent total for that division.
For example, Division A. Agriculture has 5 subdivisions. So for division A, it will have consists of 6 values (the 5 subdivisions + 0 to represent the total of the subdivision, so 6 values), subdivision is the lowest level.
So for each states/Territory, there are total of 106 values (86 subdiv, 19 divisions, plus a 0 to represent total of all divisions in that state).
so for each month, there are 106 div/subdiv * 9 states/territory * 5 sizes * 3 sectors = 14,310 rows of data.
I have attached a sample CSV for one month with values in column var1, var2 and var3 that's just randomly generated in excel so you can get an idea of the dataset structure.
How can I find the value of all the sum values represented by 0.
So I need to add up all the subdiv to get the sum for that division, then add up for division to get sum for that state/territory, then add up all the states to get total for that size, add up all the size to get that sector, add up all the sectors to get total for that month cycle. And do that for 30 month cycles. Pretty much I have to find sum of the var1, var2 and var3 for all combinations of sector, size, state, div/subdiv.
If there is a 0 in any of the columns sector, size, state, div or subdiv. I need to fine the correct values in column var1 to var3.
Any help would be highly appreciated.
... View more