topic Re: Alternative to using Proc SQL to summarize data in SAS Programming

Alternative to using Proc SQL to summarize data

carl01 — Mon, 09 Mar 2020 00:12:00 GMT

Hi,

I am trying to summarize the data using Proc SQL procedure into a table. But I have lot of columns in the table that needs summarizing. The column names that I want to Summarize range from " Amount_01, Amount_02, Amount_03 ..upto Amount_76.

It's kind of a long sql query. I was wondering if this can by shortened using some data step procedure or if we can use some loop in Proc sql. I think loops in proc sql is not allowed but I'm not sure.

Below is my query and I have just shortened the number of columns

Proc sql;
Create table test_qtr as

Select coverage
,Component
,LOB
,status
,year
,quarter
, SUM(AMOUNT_01) AS AMOUNT_01
, SUM(AMOUNT_02) AS AMOUNT_02
, SUM(AMOUNT_03) AS AMOUNT_03
, SUM(AMOUNT_04) AS AMOUNT_04
, SUM(AMOUNT_05) AS AMOUNT_05
, SUM(AMOUNT_06) AS AMOUNT_06
, SUM(AMOUNT_07) AS AMOUNT_07
, SUM(AMOUNT_08) AS AMOUNT_08
...................
..................
...................
,sum(Amount_71) as Amount_71
,sum(Amount_72) as Amount_72
,sum(Amount_73) as Amount_73
,sum(Amount_74) as Amount_74
,sum(Amount_75) as Amount_75
,sum(Amount_76) as Amount_76
from Data

Group by
coverage
,Component
,LOB
,status
,year
,quarter
order by 1,2,3,4,5,6;
quit;
run;

Thanks for your help

Re: Alternative to using Proc SQL to summarize data

Reeza — Mon, 09 Mar 2020 00:30:27 GMT

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

You want PROC MEANS.

Proc means data = yourInputData noprint;
Class listOfGroupVariables;
Var amount_01-amount_76;
Output out = want sum = / autoname;
Run;

@carl01 wrote:

Hi,

I am trying to summarize the data using Proc SQL procedure into a table. But I have lot of columns in the table that needs summarizing. The column names that I want to Summarize range from " Amount_01, Amount_02, Amount_03 ..upto Amount_76.

It's kind of a long sql query. I was wondering if this can by shortened using some data step procedure or if we can use some loop in Proc sql. I think loops in proc sql is not allowed but I'm not sure.

Below is my query and I have just shortened the number of columns

Proc sql;
Create table test_qtr as

Select coverage
,Component
,LOB
,status
,year
,quarter
, SUM(AMOUNT_01) AS AMOUNT_01
, SUM(AMOUNT_02) AS AMOUNT_02
, SUM(AMOUNT_03) AS AMOUNT_03
, SUM(AMOUNT_04) AS AMOUNT_04
, SUM(AMOUNT_05) AS AMOUNT_05
, SUM(AMOUNT_06) AS AMOUNT_06
, SUM(AMOUNT_07) AS AMOUNT_07
, SUM(AMOUNT_08) AS AMOUNT_08
...................
..................
...................
,sum(Amount_71) as Amount_71
,sum(Amount_72) as Amount_72
,sum(Amount_73) as Amount_73
,sum(Amount_74) as Amount_74
,sum(Amount_75) as Amount_75
,sum(Amount_76) as Amount_76
from Data

Group by
coverage
,Component
,LOB
,status
,year
,quarter
order by 1,2,3,4,5,6;
quit;
run;

Thanks for your help

Re: Alternative to using Proc SQL to summarize data

novinosrin — Mon, 09 Mar 2020 00:34:10 GMT

Use proc summary

proc summary data=your_data nway;
class coverage Component LOB status year quarter;
var amount01-amount76;
output out=want(drop=_:);
run;