Hi,
I'm quite new to SAS and I'm trying to perfrom a sumif function on a data set in DI, but I cant find an equivalent procedure / code that would help me with this. I have the data set below.
Bucket A | Bucket B | Value |
1 | 10 | 10 |
2 | 10 | 6 |
3 | 10 | 3 |
1 | 20 | 9 |
2 | 20 | 5 |
3 | 20 | 2 |
1 | 30 | 8 |
2 | 30 | 4 |
3 | 30 | 1 |
I want to add a column to sum the values >= Bucket A, but also to exclude the values of the previous row, so I've used a SUMIF with reative referencing. For example, the fourmula in cel D2 is, =SUMIF(A2:A10,">="&A2,C2:C10) = 48.
Bucket A | Bucket B | Value | sumif (>=A) |
1 | 10 | 10 | 48 |
2 | 10 | 6 | 21 |
3 | 10 | 3 | 6 |
1 | 20 | 9 | 29 |
2 | 20 | 5 | 12 |
3 | 20 | 2 | 3 |
1 | 30 | 8 | 13 |
2 | 30 | 4 | 5 |
3 | 30 | 1 | 1 |
Is there a way to recreate this in SAS and DI?
Many thanks,
Neil
Hi,
I can understand you might have problems grasping DI if you use Excel as a reference.
DI Studio is used for standardised ETL, where the target are detail data layers, data marts and similar.
But your requirement doesn't look like a generic table structure, feels like a report to me. Or what is the application? Sorry, I can't the logic/use of the caclulated column. Please elaborate about the data flow and use.
Perhaps this is better suited for a report tool, like a stored process.
If you need to perform this in DI, my gut feeling is that you need some kind of user written code.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.