topic Re: Proc SQL RollUps in SAS Programming

Proc SQL RollUps

buechler66 — Tue, 31 Mar 2020 12:01:06 GMT

Hi. I have the following Proc SQL that sums variables at the active_description level. Is there a way to also get an overall activity_description total by pay_data_month in the same query? I seem to remember SQL has some sort up roll up functionality. File attachment shows the current output. So the desired output would have an additional row after each grouping of pay_data_month with a sub total for all activities.

Any help here would be great. Thanks so much.

PROC SQL;
CREATE TABLE &SITE._HCR_PD_ACTVTY_SUM AS
SELECT PAY_DATA_MONTH
		, AREA
		, HCRID
		, ACTIVITY_DESCRIPTION
		, SUM(PAY_AMOUNT) FORMAT DOLLAR15.2 AS MNTHLY_PAY_AMNT
		, SUM(RATE) AS MNTHLY_RATE
		, SUM(UNITS) FORMAT 15.2 AS MNTHLY_UNITS
FROM &SITE._HCR_PD
WHERE HCRID='65036'
GROUP BY PAY_DATA_MONTH
		,AREA
		,HCRID
		,ACTIVITY_DESCRIPTION
;QUIT;

Re: Proc SQL RollUps

Patrick — Tue, 31 Mar 2020 12:10:08 GMT

All aggregate functions like sum() will be based on your grouping. You can't have different aggregation levels using a single Group By statement.

Re: Proc SQL RollUps

buechler66 — Tue, 31 Mar 2020 12:10:05 GMT

Hmm, could you share the syntax for doing it in a single sas step?

Re: Proc SQL RollUps

PaigeMiller — Tue, 31 Mar 2020 12:13:44 GMT

This type of summing over multiple different groupings is very easy in one call to PROC SUMMARY.

proc summary data=&SITE._HCR_PD(where=(HCRID='65036'));
     class pay_data_month area hcrid activity_description;
     types pay_data_month pay_data_month*area*hcrid*activity_description;
     var pay_amount rate units;
     output out=_sums_ sum=;
run;

Re: Proc SQL RollUps

Patrick — Tue, 31 Mar 2020 12:13:45 GMT

@buechler66 wrote:

Hmm, could you share the syntax for doing it in a single sas step?

Now you were faster than me deleting this comment from my post. It still would require two passes through the data as I guess you would want the "totals" added to all the rows with the sub-totals. So yes, one could have it in a single datastep using some "DOW" processing - but still two passes through the data.

Re: Proc SQL RollUps

buechler66 — Tue, 31 Mar 2020 12:43:48 GMT

Wow that's very cool. Would that be able to be altered to also get a grand total across all months?

Re: Proc SQL RollUps

PaigeMiller — Tue, 31 Mar 2020 12:47:36 GMT

@buechler66 wrote:
Wow that's very cool. Would that be able to be altered to also get a grand total across all months?

types () pay_data_month pay_data_month*area*hcrid*activity_description;

Re: Proc SQL RollUps

buechler66 — Tue, 31 Mar 2020 13:14:00 GMT

Awesome. Much simpler than Proc SQL (with no Rollup functionality). Thanks so much for taking the time to with this. I appreciate it very much!