topic Re: nested case when leads to reemerging summary stats in SAS Procedures

nested case when leads to reemerging summary stats

Batman — Mon, 10 Jun 2019 17:50:41 GMT

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;

Re: nested case when leads to reemerging summary stats

Reeza — Mon, 10 Jun 2019 18:51:26 GMT

Can you please show an example of your expected output?

Re: nested case when leads to reemerging summary stats

Batman — Mon, 10 Jun 2019 19:17:49 GMT

something like

ST 2014 2015

AL 50% 0%

AR 0% 50%

Re: nested case when leads to reemerging summary stats

PaigeMiller — Mon, 10 Jun 2019 19:29:27 GMT

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;

Re: nested case when leads to reemerging summary stats

Batman — Mon, 10 Jun 2019 19:46:33 GMT

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

Re: nested case when leads to reemerging summary stats

PaigeMiller — Mon, 10 Jun 2019 19:47:36 GMT

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.

Re: nested case when leads to reemerging summary stats

ballardw — Mon, 10 Jun 2019 23:17:23 GMT

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

Re: nested case when leads to reemerging summary stats

Batman — Thu, 13 Jun 2019 20:04:13 GMT

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