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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.