BookmarkSubscribeRSS Feed
SM8
Obsidian | Level 7 SM8
Obsidian | Level 7

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;



3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ballardw
Super User

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-dat... 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.

Reeza
Super User

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:

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;




 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 608 views
  • 2 likes
  • 4 in conversation