need help with sas sql step (trying to sum by categories)

[marleeakerson]

I am trying to create a table of sums by different categories. here is the proc means step:

proc means data=error.state_data sum;
var projpaid_1 projpaid_2;
by program state;
run;

so this gives me the sums of projpaid_1 and  projpaid_2 by state and program. I want to do the same in sas sql but cant finish the code. here is what i have: 

proc sql;
	create table state_totals as
	select sum (projpaid_1)  as TOTAL_1, 
	sum(projpaid_1)  as TOTAL_2
	from error.state_data
	group by state and program;
quit;

but it just gives me the totals of all projpaid_1 and projpaid_2, not by state and program category. How can i amend this? 

[Reeza · Accepted solution]

• You need to include state and program in your select list to have them show up in your output data set
• For the group by syntax, that is incorrect, you need to have a comma between the variables, not AND
• You're summing projpaid_1 twice, not projpaid 1 and 2. 

proc sql;
	create table state_totals as
	select state, program, sum (projpaid_1)  as TOTAL_1, 
	sum(projpaid_2)  as TOTAL_2
	from error.state_data
	group by state, program;
quit;

---

@marleeakerson wrote:

I am trying to create a table of sums by different categories. here is the proc means step:

proc means data=error.state_data sum;
var projpaid_1 projpaid_2;
by program state;
run;

so this gives me the sums of projpaid_1 and  projpaid_2 by state and program. I want to do the same in sas sql but cant finish the code. here is what i have: 

proc sql;
	create table state_totals as
	select sum (projpaid_1)  as TOTAL_1, 
	sum(projpaid_1)  as TOTAL_2
	from error.state_data
	group by state and program;
quit;

but it just gives me the totals of all projpaid_1 and projpaid_2, not by state and program category. How can i amend this? 

---

[PaigeMiller]

And if you want sums by group and state, along with state sums, along with group sums, along with overall sums, you would use PROC SUMMARY and not PROC SQL.

[Reeza]

@marleeakerson  I'm of course assuming you were aware that you can save the output from PROC MEANS directly into a data set in various forms with different levels and summaries automatically available to you. PROC SQL is not as efficient in that respect. For example in PROC MEANS you can have the overall totals and subtotals for each group included in your output as well. 

Here's some example code you can run that shows how you can capture the data from PROC MEANS.

*Create summary statistics for a dataset by a 'grouping' variable and store it in a dataset;

*Generate sample fake data;
data have;
	input ID          feature1         feature2         feature3;
	cards;
1               7.72               5.43              4.35
1               5.54               2.25              8.22 
1               4.43               6.75              2.22
1               3.22               3.21              7.31
2               6.72               2.86              6.11
2               5.89               4.25              5.25 
2               3.43               7.30              8.21
2               1.22               3.55              6.55

;
run;

*Create summary data;
proc means data=have noprint;
	by id;
	var feature1-feature3;
	output out=want median= var= mean= /autoname;
run;

*Show for display;
proc print data=want;
run;

*First done here:https://communities.sas.com/t5/General-SAS-Programming/Getting-creating-new-summary-variables-longitudinal-data/m-p/347940/highlight/false#M44842;
*Another way to present data is as follows;

proc means data=have stackods nway n min max mean median std p5 p95;
    by id;
    var feature1-feature3;
    ods output summary=want2;
run;

*Show for display;
proc print data=want2;
run;