topic Re: Grouping In PROC PRINT step in SAS Procedures

Grouping In PROC PRINT step

Jazz_SAS — Mon, 12 Jul 2010 09:20:42 GMT

Hello People. I'm learner in SAS.

I have a simple query in access:

SELECT Tbl.Location, Tbl.Jobcode, Count(Tbl.EmpId), Sum(Tbl.Salary)
FROM Tbl
GROUP BY Tbl.Location, Tbl.Jobcode;

I need to display the same results in SAS. The dataset is created.

The code i'm trying to write is as follows:

PROC SORT DATA = msdq.crew OUT=work.Tbl;
BY Location Jobcode;
RUN;

PROC PRINT DATA = Tbl;
ID Location Jobcode;
BY Location Jobcode;
VAR Salary;
SUM Salary;
RUN;

I need to write Count(EmpId) and Sum(Salary)

And also the above code does not group the two variables; it creates a Sub-total and a grand total.

How to group. Help me out plz ?

Re: Grouping In PROC PRINT step

Patrick — Mon, 12 Jul 2010 09:36:40 GMT

Why don't you just use this already tested SQL code in SAS?

Proc sql;
SELECT Tbl.Location, Tbl.Jobcode, Count(Tbl.EmpId), Sum(Tbl.Salary)
FROM Tbl
GROUP BY Tbl.Location, Tbl.Jobcode;
quit;

Re: Grouping In PROC PRINT step

Ksharp — Mon, 12 Jul 2010 11:32:18 GMT

proc report or proc tabulate would be a better choice.
and Cynthia@sas.com can do it better.hehe.
This is just an example.

[pre]
proc tabulate data=sashelp.class;
class sex name;
var weight height;
table sex*name,weight*n height*sum;
run;
[/pre]

Ksharp

Re: Grouping In PROC PRINT step

Cynthia_sas — Mon, 12 Jul 2010 14:38:53 GMT

Hi:
Ksharp has posted one possible solution. Another solution (if you want a "grouped" report (such as without name), would be to take her TABULATE code and remove the "*name" from the TABLE statement.

Otherwise, you can also achieve a grouped or summary report with PROC REPORT, as well, consider the program below. In this program, the variables SEX and AGE are group variables and so the "detailed" observations will collapse into groups of unique ages within each sex value. Then the Weight variable is used to get the N statistic to count the observations in each SEX/AGE group and the HEIGHT variable is used to get the SUM of HEIGHTS (not very meaningful, but it is only meant to show the use of the SUM statistic).

cynthia
[pre]
proc report data=sashelp.class nowd;
column sex age weight height;
define sex / group;
define age / group;
define height / sum;
define weight / n 'Count';
rbreak after / summarize;
run;
[/pre]

Re: Grouping In PROC PRINT step

Ksharp — Mon, 12 Jul 2010 15:24:55 GMT

Hi. Cynthia. I am boy ,thirty years old.hehe.

Re: Grouping In PROC PRINT step

Cynthia_sas — Mon, 12 Jul 2010 16:00:45 GMT

Sorry for the misunderstanding.