BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Batman
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

7 REPLIES 7
Reeza
Super User
Can you please show an example of your expected output?
Batman
Quartz | Level 8

something like

 

ST       2014   2015

AL           50%      0%

AR             0%     50%

PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Batman
Quartz | Level 8

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ballardw
Super User

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

 

 

Batman
Quartz | Level 8

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1002 views
  • 0 likes
  • 4 in conversation