topic Re: How to do a summary for the totals for each quarter and send output to Excel? in SAS Programming

How to do a summary for the totals for each quarter and send output to Excel?

SM8 — Tue, 26 May 2020 17:58:57 GMT

So, I'm looking to do a summary of a dataset using proc tabulate to get totals for the visitcount and payment variables for each quarter. I also want it so that my output is sent directly to excel. How can I do this?

proc sql;
create table statesums as
select count(pkey) as visitcount, state,
date, code, sum(dis) as payment
from medcla
where state in ('NY','PA','NJ')
group by state, date, code;
quit;

proc sort data = statesums; 
by state code; run;

proc transpose data = statesums out=visits prefix=visitcount;
var visitcount; id date; by =state hcpcs;
run;

proc transpose data = statesums out=payments prefix=dollars;
var payment; id date; by state code;
run;

proc sql;
create table totals as
select * from vists as x left join payments as y
on x.state = y.state
and x.code = y.code;
quit;

Re: How to do a summary for the totals for each quarter and send output to Excel?

PaigeMiller — Tue, 26 May 2020 17:57:59 GMT

Can you show us what the output ought to look like?

Honestly, I don't think any of your code is needed, PROC REPORT ought to produce the table directly from the original data.

Re: How to do a summary for the totals for each quarter and send output to Excel?

ballardw — Tue, 26 May 2020 18:53:41 GMT

If you are going to ask a bunch of related questions you really should provide an example data set we can work with.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: How to do a summary for the totals for each quarter and send output to Excel?

Reeza — Tue, 26 May 2020 19:27:59 GMT

No idea how your code relates to your question, here's how I'd do it.

ods excel file="/folders/myfolders/demo.xlsx" style=meadow;

proc tabulate data=sashelp.stocks;
class stock date;
format date yyq6.;
var open;
table date*stock, open*MIN open*MAX;
run;

ods excel close;

@SM8 wrote:

proc sql;
create table statesums as
select count(pkey) as visitcount, state,
date, code, sum(dis) as payment
from medcla
where state in ('NY','PA','NJ')
group by state, date, code;
quit;

proc sort data = statesums; 
by state code; run;

proc transpose data = statesums out=visits prefix=visitcount;
var visitcount; id date; by =state hcpcs;
run;

proc transpose data = statesums out=payments prefix=dollars;
var payment; id date; by state code;
run;

proc sql;
create table totals as
select * from vists as x left join payments as y
on x.state = y.state
and x.code = y.code;
quit;