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

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?

 

pic1.png

1 ACCEPTED SOLUTION

Accepted Solutions
jess11111111
Fluorite | Level 6

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;

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

 

 

jess11111111
Fluorite | Level 6

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.

 

ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ
Hi,
I'm not sure about what you show above. For the SC rows only, I get 7,400 not 14,800:
1800+1800+1400+1000+1400 = 7,400

Wouldn't the sum for SC be only the SC rows?

Doing some desk checking. It looks like a PROC TABULATE output or a PROC REPORT output to me. But without a test dataset, no one is likely to make data and write a report program, too.

cynthia
jess11111111
Fluorite | Level 6
Thanks, I completely understand, just wasn't able to get the data in a
workable format today and it's also sensitive so I'd have to fake it.
Anyway, I figured it out with proc tabulate. I wasn't writing my table
statement correctly.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
jess11111111
Fluorite | Level 6

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 1008 views
  • 0 likes
  • 3 in conversation