@cactooos wrote:
Thank you both, Yikes when I look at the solution it seems to be so easy to came up with but this is my 2nd month with SAS. This is exactly what I was looking for. My data set is a table from proc sql grouped by var1 and date so no duplicated values expected. Thanks
Is the result from your Proc SQL used only to create the shown summary as a step to creating the desired table? If so you may want to share the Proc SQL code as it may not be needed at all. Proc Tabulate (and Proc Report) will do a lot of counts and groupings. The SQL may only be needed if you are getting the values from multiple data sets, using case statements to do fancy selection for counting or using a DISTINCT to reduce the data.
I am guilty of sometimes overusing one tool because I am more familiar with it even though another I have may be better. If you started with Proc SQL because of prior experience in SQL that may be the case.
Something to consider with Proc Tabulate: You can create multiple tables of different structures with one call to the procedure.
Proc tabulate data=sashelp.class;
class sex age;
var height weight;
table sex,
age
;
Table sex*(height weight)*(min mean max),
age
;
table sex*age,
(height weight) * (n mean std)
;
table sex*height age*weight,
n mean std
;
table sex,
age,
(height weight) * (n mean std)
;
run;
As a not very sophisticated example. It does demonstrate that you can get different layouts. With more variable you could mix and match as (possibly) desired.
Caveat: Tabulate with missing values for ANY class variable will exclude the observation(s) affected unless the option MISSING is applied to the Class statement.
If you find yourself thinking about writing SQL code with a lot of Count(var) as varcount clauses you may want to investigate one of the other procedures. And Proc Tabulate and Report can create output data sets that admittedly take some getting used to work with. But the amount of other code that might be needed by other procedures can make it worthwhile. And when there are multiple tables as above the output dataset will contain a variable that indicates which table the rows of generated data are from.
... View more