BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carl01
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Use proc summary

 

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

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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


 

novinosrin
Tourmaline | Level 20

Use proc summary

 

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 855 views
  • 2 likes
  • 3 in conversation