nested case when leads to reemerging summary stats

[Batman]

The sql procedure below gave me "The query requires remerging summary statistics back with the original data."    I'd like to group the results so I have one row for each st and one column for each yr.

data a;
input yr 4. st $2. typ 1.;
datalines;
2014AL1
2014AL2
2014AL3
2014AL1
2015AL2
2015AL3
2014AR1
2015AR1
2015AR2
;
run;

proc sql;
select st,
case when yr=2014 then mean(case when typ=1 then 1 else 0 end) else . end as pct14,
case when yr=2015 then mean(case when typ=1 then 1 else 0 end) else . end as pct15
from a
group by st;
quit;

[Reeza]

Can you please show an example of your expected output?

[Batman]

something like

ST       2014   2015

AL           50%      0%

AR             0%     50%

[PaigeMiller · Accepted solution]

Sometimes, SQL isn't the right tool. 

data a;
input yr 4. st $2. typ 1.;
if typ=1 then value=1; else value=0;
datalines;
2014AL1
2014AL2
2014AL3
2014AL1
2015AL2
2015AL3
2014AR1
2015AR1
2015AR2
;
run;

proc report data=a;
    columns st yr,value;
	define st/group "State";
	define yr/across "Year";
	define value/mean "Percent" format=percent7.0;
run;

[Batman]

ok, I was trying to avoid the additional data step but I accept that sql isn't well equipped in this case.

[PaigeMiller]

But it's not an "additional step", where you had PROC SQL before, you now have PROC REPORT, that's the same number of steps.

[ballardw]

---

@PaigeMiller wrote:

But it's not an "additional step", where you had PROC SQL before, you now have PROC REPORT, that's the same number of steps.

---

AND fewer lines of code.

[Batman]

The if statement would have required an additional step given my original data step, but I accept your larger point.