I’m having issues getting report to come out as desired. Below is a snippet of what the data looks like then below it is a snapshot of what I’d like the report to look like. It seemed to me that it would be simple, but I can’t seem to get it to group by state or to figure out how to count the number of observations by state. Any help would literally make my week. I basically need it to group by everything, but separated by state.
I've looked up tons on proc freq, summary, sql, print, etc. Help?
Here's the code I ended up using, in case it's ever useful to someone:
proc tabulate data=*** format=9.;
class state amtid;
var amount percent number_of_payments;
table state*(amtid all='Total'),
N amount percent*mean number_of_payments*mean=' ';
run;
If you provide data in the form of a data step (not a picture, most of us do not have the time to enter data from sratch) then we can test code. Instructions here will show how to create datastep code from a SAS data set: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
What would N_state be for the shown example input data?
In the final report is Amount the max, min, mean of the amount within the State and amtid combination?
Same question for Percent and N_Payments
And show the values of the SUMs for the total row.
The original dataset is very large...so I'm sitting here waiting for that macro to run. In the meantime:
N_State would be the count for that state within that AMTID. The Amount column is a set amount that correlates to both amtid and number of payments. I.e. no amtid has more than one number of payments or amount. This goes for percent as well. As far as the last row for total goes, I'm looking most importantly to sum the occurences of state within that amtid and to sum "amount."
For what I gave you, sum of state would be 5, amount would be 14800, and percent 26.99.
If your original data is large you may want to make an example set:
data example;
set mylarge data (obs=50);
run;
and then create the data step from that set.
Here's the code I ended up using, in case it's ever useful to someone:
proc tabulate data=*** format=9.;
class state amtid;
var amount percent number_of_payments;
table state*(amtid all='Total'),
N amount percent*mean number_of_payments*mean=' ';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.