topic Re: How do i combine two tables and do a summary for the totals? in New SAS User

How do i combine two tables and do a summary for the totals?

SM8 — Tue, 26 May 2020 14:35:16 GMT

So,

I have two tables that I created using proc sql and then transposed.

Here is the code:

proc sql;
create table summary as
select count(distinct key) as vistcnt, state,
date, codes, sum(disbursmnt) as paymnt
from meds
where state in ('NY','PA','NJ')
group by state, date, codes;
quit;

proc sort data = summary;
by state codes;
run;

proc transpose data = summary out=visits prefix=visitcount;
var vistcnt; id date; by state codes;
run;

proc transpose data = summary out=payments prefix=dollars;
var paymnt; id date; by state codes;
run;

How can I put these two together and do a summary for the total of vistcnt and paymnt?

Re: How do i combine two tables and do a summary for the totals?

Kurt_Bremser — Tue, 26 May 2020 14:28:55 GMT

Summarize before you transpose. Long datasets are always easier for processing than wide ones.

For detailed help, provide the untransposed dataset in a data step with datalines.

Re: How do i combine two tables and do a summary for the totals?

SM8 — Tue, 26 May 2020 14:35:42 GMT

Here is the code before the transpose:

proc sql;
create table summary as
select count(distinct key) as vistcnt, state,
date, codes, sum(disbursmnt) as paymnt
from meds
where state in ('NY','PA','NJ')
group by state, date, codes;
quit;

proc sort data = summary;
by state codes;
run;