DATA Step, Macro, Functions and more

Grouping by "age" using formats, then summarize

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Grouping by "age" using formats, then summarize

[ Edited ]

COUBT.jpeg


Accepted Solutions
Solution
‎03-28-2018 10:27 AM
Super User
Posts: 22,874

Re: agegrp

proc format;
value agegroup_fmt
low - 10 = '<11'
11, 12 = '11-12'
13-14 = '13-14'
15-16 = '15-16'
17-high = '>16'
;
run;

proc tabulate data=sashelp.class out=want ;
class age / preloadfmt  exclusive;
format age agegroup_fmt.;
table age / printmiss misstext='0';
run;

View solution in original post


All Replies
Super User
Posts: 22,874

Re: agegrp

[ Edited ]
Posted in reply to rvsidhu035

What is your question? 

What have you tried so far?

 


@rvsidhu035 wrote:

COUBT.jpeg


 

Super User
Posts: 22,874

Re: agegrp

Here's an example with BMI instead;

 

https://gist.github.com/statgeek/227565537f3dffcd8627f1fe2eff844f

 

Pretty much the same idea.

 

Or I believe there's an example here as well, same BMI, but a step by step walk through

http://video.sas.com/detail/videos/sas-analytics-u/video/4573016759001/performing-conditional-logic-...

Contributor
Posts: 56

Re: agegrp

from sashelp.class data set develop above table

Super User
Posts: 22,874

Re: agegrp

Posted in reply to rvsidhu035

What have you tried so far? I don't really feel like doing your homework but I'm happy to help while you're doing it. 

 

Contributor
Posts: 56

Re: agegrp

proc sql;
create table wantk as select 
case when age<11 then '<11' else
	case when age in (11,12) then '11-12' else
		case when age in (13,14) then '13-14' else
			case when age in (15,16) then '15-16' else
				case when age>16 then '>16' else ' ' end
			 end end end end as agegrp,
	count(CALCULATED ageGRP) as count
	from sashelp.class group by ageGRP;quit;
data dummy;
input agegrp $ @@;
cards;
<11 11-12 13-14 15-16 >16
;
run;

proc sql;
create table want3 as select dummy.*,wantk.count from dummy as x  left join wantk as y on 
x.agegrp eq y.agegrp;quit;
proc sql;
create table anser as select 
case when count eq . then 0 else count end as count,agegrp
from want3 order by agegrp desc;
Super User
Posts: 22,874

Re: agegrp

Posted in reply to rvsidhu035

Besides the order that looks correct. Is there something else you're looking for?

 

The answer to your third question is the PRELOADFMT in PROC TABULATE with the MISSING option specified. That would actually do all three at once. 

 

Your current code is SQL, so this would be a very different approach but would take advantage of the automatic SAS processing. 

 

 

 

 

Solution
‎03-28-2018 10:27 AM
Super User
Posts: 22,874

Re: agegrp

proc format;
value agegroup_fmt
low - 10 = '<11'
11, 12 = '11-12'
13-14 = '13-14'
15-16 = '15-16'
17-high = '>16'
;
run;

proc tabulate data=sashelp.class out=want ;
class age / preloadfmt  exclusive;
format age agegroup_fmt.;
table age / printmiss misstext='0';
run;
PROC Star
Posts: 2,231

Re: agegrp

Posted in reply to rvsidhu035

You are been entered in the contest for the least useful thread title of the year. Smiley Happy

Congratulations!

 

Community Manager
Posts: 3,364

Re: agegrp

I fixed the title -- though the question looks like a homework assignment...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 119 views
  • 2 likes
  • 4 in conversation