Hi,
I want to create a completion variable (if PinT =1 then Completion=cost/subtotal) then I want to add this variable into my proc tabulate.
Here is what I have:
Here is what I want:
Here is the sample data I am working with:
data have;
input Pint Cost State$ Year Clients ;
datalines;
1 4789 WY 2023 3
0 1589 WI 2023 8
0 2569 WY 2024 15
0 5896 WI 2020 1
1 2000 WY 2024 8
1 4000 WI 2021 9
0 400 WI 2020 15
0 2000 WY 2021 12
0 2000 WY 2021 10
0 2 WI 2020 7
0 14 WY 2020 2
0 3000 WI 2022 8
0 1000 WY 2022 7
0 43 WI 2021 4
0 800 WY 2020 2
0 20 WI 2021 2
0 1000 WY 2021 1
0 500 WI 2020 7
0 42 WY 2020 9
0 6000 WY 2023 7
1 5000 WI 2023 5
1 258 WI 2024 7
0 2569 WI 2024 9
1 500 WI 2020 2
1 42 WI 2020 2
1 256 WY 2020 8
1 478 WY 2022 2
1 879 WY 2021 8
1 300 WI 2022 2
;;;
run;
Proc tabulate data=have format=dollar12. out=want1;
class State Year Pint ;
var cost ;
table State='State'*(Pint all="Subtotal") ,(cost='$'* Year='Year')*sum=""/printmiss ;
run;
That is not easy for PROC TABULATE.
But easy for PROC SQL and PROC REPORT.
data have;
input Pint Cost State$ Year Clients ;
datalines;
1 4789 WY 2023 3
0 1589 WI 2023 8
0 2569 WY 2024 15
0 5896 WI 2020 1
1 2000 WY 2024 8
1 4000 WI 2021 9
0 400 WI 2020 15
0 2000 WY 2021 12
0 2000 WY 2021 10
0 2 WI 2020 7
0 14 WY 2020 2
0 3000 WI 2022 8
0 1000 WY 2022 7
0 43 WI 2021 4
0 800 WY 2020 2
0 20 WI 2021 2
0 1000 WY 2021 1
0 500 WI 2020 7
0 42 WY 2020 9
0 6000 WY 2023 7
1 5000 WI 2023 5
1 258 WI 2024 7
0 2569 WI 2024 9
1 500 WI 2020 2
1 42 WI 2020 2
1 256 WY 2020 8
1 478 WY 2022 2
1 879 WY 2021 8
1 300 WI 2022 2
;;;
run;
proc sql;
create table want as
select state,' '||put(pint,best8. -l) as pint length=80,year,put(sum(cost),dollar32. -l) as cost length=80
from have
group by state,pint,year
union
select state,' Subtotal',year,put(sum(cost),dollar32. -l) as cost
from have
group by state,year
union
select state,'Completion',year,put((select sum(cost) from have where state=a.state and year=a.year and pint=1)/sum(cost),percent8.2 -l) as cost
from have as a
group by state,year
;
quit;
proc report data=want nowd;
column state pint cost,year;
define state/group;
define pint/group;
define year/across;
define cost/group '$';
run;
That is not easy for PROC TABULATE.
But easy for PROC SQL and PROC REPORT.
data have;
input Pint Cost State$ Year Clients ;
datalines;
1 4789 WY 2023 3
0 1589 WI 2023 8
0 2569 WY 2024 15
0 5896 WI 2020 1
1 2000 WY 2024 8
1 4000 WI 2021 9
0 400 WI 2020 15
0 2000 WY 2021 12
0 2000 WY 2021 10
0 2 WI 2020 7
0 14 WY 2020 2
0 3000 WI 2022 8
0 1000 WY 2022 7
0 43 WI 2021 4
0 800 WY 2020 2
0 20 WI 2021 2
0 1000 WY 2021 1
0 500 WI 2020 7
0 42 WY 2020 9
0 6000 WY 2023 7
1 5000 WI 2023 5
1 258 WI 2024 7
0 2569 WI 2024 9
1 500 WI 2020 2
1 42 WI 2020 2
1 256 WY 2020 8
1 478 WY 2022 2
1 879 WY 2021 8
1 300 WI 2022 2
;;;
run;
proc sql;
create table want as
select state,' '||put(pint,best8. -l) as pint length=80,year,put(sum(cost),dollar32. -l) as cost length=80
from have
group by state,pint,year
union
select state,' Subtotal',year,put(sum(cost),dollar32. -l) as cost
from have
group by state,year
union
select state,'Completion',year,put((select sum(cost) from have where state=a.state and year=a.year and pint=1)/sum(cost),percent8.2 -l) as cost
from have as a
group by state,year
;
quit;
proc report data=want nowd;
column state pint cost,year;
define state/group;
define pint/group;
define year/across;
define cost/group '$';
run;
When I attempt to add client count into the table I get the following error:
WARNING: A table has been extended with null columns to perform the UNION set operation. Do you know why I am getting this error?
proc sql;
create table want as
select state,' '||put(pint,best8. -l) as pint length=80,year,put(sum(cost),dollar32. -l) as cost length=80,put(sum(Clients),comma10. -l) as Clients length=80
from have
group by state,pint,year
union
select state,' Subtotal',year,put(sum(cost),dollar32. -l) as cost
from have
group by state,year
union
select state,' Subtotal',year,put(sum(Clients),comma10. -l) as Clients
from have
group by state,year
union
select state,'Completion Factor',year,put((select sum(cost) from have where state=a.state and year=a.year and pint=1)/sum(cost),percent8.2 -l) as cost
from have as a
group by state,year
;
quit;
So what kind of report would you like to see ? This one ?
data have;
input Pint Cost State$ Year Clients ;
datalines;
1 4789 WY 2023 3
0 1589 WI 2023 8
0 2569 WY 2024 15
0 5896 WI 2020 1
1 2000 WY 2024 8
1 4000 WI 2021 9
0 400 WI 2020 15
0 2000 WY 2021 12
0 2000 WY 2021 10
0 2 WI 2020 7
0 14 WY 2020 2
0 3000 WI 2022 8
0 1000 WY 2022 7
0 43 WI 2021 4
0 800 WY 2020 2
0 20 WI 2021 2
0 1000 WY 2021 1
0 500 WI 2020 7
0 42 WY 2020 9
0 6000 WY 2023 7
1 5000 WI 2023 5
1 258 WI 2024 7
0 2569 WI 2024 9
1 500 WI 2020 2
1 42 WI 2020 2
1 256 WY 2020 8
1 478 WY 2022 2
1 879 WY 2021 8
1 300 WI 2022 2
;;;
run;
proc sql;
create table want as
select state,' '||put(pint,best8. -l) as pint length=80,year,put(sum(cost),dollar32. -l) as cost length=80
,' Cost' as label length=80
from have
group by state,pint,year
union
select state,' Subtotal',year,put(sum(cost),dollar32. -l) as cost
,' Cost' as label length=80
from have
group by state,year
union
select state,'Completion',year,
put((select sum(cost) from have where state=a.state and year=a.year and pint=1)/sum(cost),percent8.2 -l) as cost
,' Cost' as label length=80
from have as a
group by state,year
union all
select state,' '||put(pint,best8. -l) as pint length=80,year,put(sum(Clients),comma10. -l) as Clients
,'Count' as label length=80
from have
group by state,pint,year
union
select state,' Subtotal',year,put(sum(Clients),comma10. -l) as Clients
,'Count' as label length=80
from have
group by state,year
union
select state,'Completion',year,
put((select sum(Clients) from have where state=a.state and year=a.year and pint=1)/sum(Clients),percent8.2 -l) as Clients
,'Count' as label length=80
from have as a
group by state,year
;
quit;
proc report data=want nowd;
column state pint cost,year,label;
define state/group;
define pint/group;
define year/across;
define label/across '';
define cost/group '$';
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.