Creating a new variable from existing variables and adding it into a proc tabulate

[Whitlea]

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;

[Ksharp · Accepted solution]

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;

[Whitlea]

Thank you!!

[Whitlea]

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;

[Ksharp]

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;