SAS Query

[Mjooda]

Good morning!!

I kindly need help in writing this query for a practice pull.

If I can get help from anyone ?

total death counts over the 5 year period 2015-2019 at the zipcode level

by age groups: under 5, 5-14, 15-24, 25-34, 35-44, 45-54, 55-64, 65-74, 75-84, and 85 and above. 

proc sql;
create table Brooking as
select count (CRESIDZIP_EXT)
from CLEANED.death2020
where age<=5 and age>=85;
quit;

I have tried several queries but still not getting the info needed. 

Thanks 

[japelin]

how about these two codes.

proc sql;
create table Brooking as
  select count (CRESIDZIP_EXT),
    case 
      when(    age< 5) then 0
      when( 5<=age<14) then 1
      when(15<=age<24) then 2
      when(25<=age<34) then 3
      when(35<=age<44) then 4
      when(45<=age<54) then 5
      when(55<=age<64) then 6
      when(65<=age<74) then 7
      when(75<=age<84) then 8
      when(85<=age)    then 9
      else .
    end as age_group
  from CLEANED.death2020
  group by age_group;
quit;

or

proc sql;
create table Brooking as
  select count (CRESIDZIP_EXT),
         round(age/10,1) as age_group
  from CLEANED.death2020
  group by age_group;
quit;

[Mjooda]

Okay thanks would try that. 

[AMSAS]

You might want to consider using PROC FORMAT and PROC FREQ to do this

/* Create some sample data */
data bulk ;
	set sashelp.class ;
	do i=1 to 5 ;
		output ;
	end ;
run ;

/* Creatge a custom format */
/* https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1xidhqypi0fnwn1if8opjpqpbmn.htm */
proc format ;
	value age 
		11-12   = "11-12" 
		13-14   = "13-14"
		15-high = "Over 14"
		;
run ;

proc sql ;
	/* counts unformatted */
	create table counts as
	select 
		age, count(*)
	from
		bulk 
	group by age ;

	;
	/* counts formatted */
	create table countsF as
	select 
		putn(age,"age.") as ageF , count(*)
	from
		bulk 
	group by ageF ;

	;

run ;

/* If you just want counts, then PROC FREQ might be a better option */
/* https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/procstat/procstat_freq_toc.htm */
proc freq data=bulk noprint ;
	format age age. ;
	table age / out=freqOut ;
run ;

[Mjooda]

Thank you

[Sajid01 · Accepted solution]

Hello @Mjooda 
One approach is to use proc format and proc means.
I have explained by means of an example,

Proc format;
value agegroup
       0  -< 5   = "Infant"
        5-<15   = "1"
       15-<25   = "2"
       25 -<35   = "3"
       35-<45   = "4"
       45-<55   = "5"
       55-<65   = "6"
       65-<75   = "7"
       75-<85   = "8"
       85- HIGH  = "Senior";
data class2;
Format age agegroup.;
set sashelp.class;
run;
       
proc means data=class2 sum  nonobs ;
Class Age ;
output out=sumout(Rename=(_FREQ_=NUM_STUDENTS AGE=AGE_GRP) WHERE=(_TYPE_=1) ) sum(weight) =GP_WT;;
run;
data sumout (drop=_TYPE_);
set sumout ;
run;

The final output will be as follows

[Mjooda]

Thank you!!

[Amir]

Hi,

Welcome to the SAS forums!

It is good that you are thanking others for their proposed solutions.

If you are happy with what you have received, then a further show of gratitude is to mark one of the responses as a solution (not this post) as this increases their solution count.

Also, this way other contributors can see that nothing further is needed.

Thanks & kind regards,

Amir.