topic Re: Age grouping in SAS Programming

Age grouping

PrinceAde — Sun, 09 Apr 2023 10:19:26 GMT

I'm trying to group derive age group as shown below.
<65
>=65
<75
>=75
data ydata;
set mdata;
if age < 65 then age_group = "<65";
else if age >= 65 then age_group = ">=65";
else if age < 75 then age_group = "<75";
else if age >= 75 then age_group = ">=75";
run;
When I ran this code it as I expected ignored the last two statements.
Is it possible to like that at all?
What can I do?

Thank you!

Re: Age grouping

PaigeMiller — Sun, 09 Apr 2023 17:06:36 GMT

It ignores the last two groupings because your logic is incorrect. After the first two tests, the person's age is either <65 or >=65, one of those two conditions has to be true and so the remaining tests are not performed. But we're still left guessing as to what you DO want out of this data — you haven't told us. So a guess: perhaps you want something like this:

if age < 65 then age_group = "<65";
else if age >= 65 and age<75 then age_group = "65-75";
else if age >= 75 then age_group = ">=75";

A better solution is to use custom formats and then use the formats in your analysis; rather than create a new variable which is a character string. Example:

proc format;
    value agef low-<65='<65' 65-<75='65-75' 75-high='>=75';
run;

proc means data=have;
    class age;
    format age agef.;
    var whatever;
run;

Re: Age grouping

AhmedAl_Attar — Sun, 09 Apr 2023 10:28:55 GMT

Hi @PrinceAde

SAS Formats are the perfect solution to this.

Check this paper for ilustartion The Power of the FORMAT Procedure

Hope this helps

Re: Age grouping

Kurt_Bremser — Sun, 09 Apr 2023 10:32:42 GMT

I can only reinforce what has already been said: use a format.

Re: Age grouping

ballardw — Sun, 09 Apr 2023 17:07:20 GMT

You have another syntax issue involved related to the length of the result variable.

If you run:

data example;
 input age;
if age < 65 then age_group = "<65";
else if age >= 65 then age_group = ">=65";
else if age < 75 then age_group = "<75";
else if age >= 75 then age_group = ">=75";
datalines;
63
64
65
66
67
;


proc print data=example;
run;

You will get a result of

Obs age age_group 
1 63 <65 
2 64 <65 
3 65 >=6 
4 66 >=6 
5 67 >=6

Why ">=6" you may ask? You did not define length for your Age_group variable. So the first use of the variable in the assignment:

if age < 65 then age_group = "<65";

established a length of three characters, enough to hold <65.

This is another thing that formats will avoid. The groups created by formats will be honored by reporting and analysis procedures and in most places for graphing. So you don't even have to add a variable at all.

Re: Age grouping

PrinceAde — Sun, 09 Apr 2023 18:16:23 GMT

I'm working on a sdtmig dataset, I have the Age variable;

I'm supposed to generate age_group;

<65
>=65
<75
>=75

and then generate the count and percent of the age_group using proc freq.

It is a case study.

Re: Age grouping

PrinceAde — Sun, 09 Apr 2023 18:18:20 GMT

Thank you very much sir, I will the proc format.

This is a brief on what I'm trying to achieve;

I'm working on a sdtmig dataset, I have the Age variable;

I'm supposed to generate age_group;

<65
>=65
<75
>=75

and then generate the count and percent using proc freq.

Re: Age grouping

PrinceAde — Sun, 09 Apr 2023 18:21:37 GMT

Ok sir. Thank you.

Re: Age grouping

PrinceAde — Sun, 09 Apr 2023 18:42:45 GMT

Hi, I actually added a length statement before running the code.

"<65"

">=65"

"<75"

">=75"

Is this possible to group like that at all?

Re: Age grouping

PaigeMiller — Sun, 09 Apr 2023 20:48:42 GMT

@PrinceAde wrote:

I'm working on a sdtmig dataset, I have the Age variable;

I'm supposed to generate age_group;

<65
>=65
<75
>=75

and then generate the count and percent of the age_group using proc freq.

@PrinceAde Thank you but this does not explain what you want. If a person is 63 years old, should this person be in both the <65 and <75 group? 63 is <65 and 63 is also <75. That's what it sounds like you are asking for. Please explain and clear this up.

Whatever it is you want, use FORMATs.

Re: Age grouping

ballardw — Sun, 09 Apr 2023 23:00:29 GMT

@PrinceAde wrote:

I'm working on a sdtmig dataset, I have the Age variable;

I'm supposed to generate age_group;

<65
>=65
<75
>=75

and then generate the count and percent of the age_group using proc freq.

It is a case study.

If, as in @PaigeMiller's example a person that is of age 63 is supposed to be counted in both the <65 and the <75 age groups you have a very limited choice. Proc freq will not do this with a single variable. In fact the only approach that I think is reasonable would not use such a value at all as the tools reside in special places of format definition and 4 procedures that can use them.

One of the reasons we so often ask for example data and the result of your process for that given data is that it does not require pulling one fact at a time.

Here is a small enough example data set that you should be able to count categories you want. SHOW us the result using this data set that you would want from Proc Freq. Don't write any code to assign values. Manually count and show us the result of how many are in each category. Or just list which category belongs next to each value.

data example;
   input age;
datalines;
55
57
63
64
64
64
65
66
67
67
68
68
69
70
71
71
71
72
72
73
74
75 
76
76
77
77
78
;

Re: Age grouping

PrinceAde — Mon, 10 Apr 2023 05:33:47 GMT

Thank you.

<65 : 55 57 63 64 64. i.e <65 should appear 5 times in the column

>=65 : 65 66 67 67 68 68 68 69 70 71 71 71 71 72 72 73 74 76 76 77 77 78. >=65 should appear 22 times

<75 : 55 57 63 64 64 64 65 66 67 67 68 68 69 70 71 71 71 72 72 73 74. <75 should appear 21 times

>=75 : 75 76 76 77 77 78 >=75 should appear 6 times.

That is what I expect for each category if that is possible at all.

Re: Age grouping

PaigeMiller — Mon, 10 Apr 2023 11:32:34 GMT

Thank you for clearing this up.

Yes it is possible.

You will need to run PROC FREQ twice, once using a format that assigns people to the categories <65 and >=65, and once using a format that assigns people to the categories <75 and >=75.

proc format;
    value one low-<65='<65' 65-high='>=65';
    value two low-<75='<75' 75-high='>=75';
run;
proc freq data=have;
    tables age;
    format age one.;
run;
proc freq data=have;
    tables age;
    format age two.;
run;

Re: Age grouping

ballardw — Mon, 10 Apr 2023 15:52:29 GMT

@PrinceAde wrote:

Thank you.

<65 : 55 57 63 64 64. i.e <65 should appear 5 times in the column

>=65 : 65 66 67 67 68 68 68 69 70 71 71 71 71 72 72 73 74 76 76 77 77 78. >=65 should appear 22 times

<75 : 55 57 63 64 64 64 65 66 67 67 68 68 69 70 71 71 71 72 72 73 74. <75 should appear 21 times

>=75 : 75 76 76 77 77 78 >=75 should appear 6 times.

That is what I expect for each category if that is possible at all.

This is possible for limited operations with a custom format defined as MULTILABEL. One thing about such formats is that only Procs Tabulate, Report, Means and Summary (currently) honor them.

data example;
   input age;
datalines;
55
57
63
64
64
64
65
66
67
67
68
68
69
70
71
71
71
72
72
73
74
75 
76
76
77
77
78
;


Proc format;
value myagegroups (multilabel)
low -< 65 = '<65'
65  - high = '>=65'
low -< 75 = '<75'
75  - high = '>=75'
;
run;

proc tabulate data=example;
   class age /mlf ;
   format age myagegroups.;
   table age,
         n
   ;
run;

Which results in

     n
age 6 
<65  
<75  21 
>=65 21 
>=75 6

If actual order of output is critical I leave as an exercise to an interested reader to experiment with the Proc Format options Notsorted, the order of the assignment statements in the format values and the ORDER= options in Proc Tabulate as they interact.

Note that the MLF option on the Class statement tells SAS to expect a MULTILABEL defined format and use the feature if actually defined in the format.

Again, those 4 procedures are the only ones that will do what you are wanting with a single variable and single pass through the data.

Re: Age grouping

PrinceAde — Tue, 11 Apr 2023 13:00:14 GMT

I did as you said using if else condition to create a new variable agegroup;

<65

>=65

as agroupx then proc freq

<75

>=75

as agegroupy then proc freq.

Thank you very much. Very helpful.

Re: Age grouping

PaigeMiller — Tue, 11 Apr 2023 13:08:07 GMT

@PrinceAde wrote:

I did as you said using if else condition to create a new variable agegroup;

<65

>=65

as agroupx then proc freq

<75

>=75

as agegroupy then proc freq.

Thank you very much. Very helpful.

But I did not say to create new variables. I specifically said use formats. Part of the purpose of having these discussions is not just to provide an answer, but to point you in the direction of better methods. Formats are better for a lot of reasons than creating new variables, and apparently despite formats being mentioned by many people, you chose not to use this better method.

Re: Age grouping

PrinceAde — Tue, 11 Apr 2023 13:05:40 GMT

Thank you very much sir. I was able to resolve. I did proc freq for <65 and >=65 and another one for <75 and >=75.

Re: Age grouping

PrinceAde — Tue, 11 Apr 2023 13:40:48 GMT

Yes, I initially used proc format, however I realized that I still needed to generate proc means for the initial age variable, hence the reason I created the agegroup variable to be able to generate the proc frequencies separately.

Re: Age grouping

PaigeMiller — Tue, 11 Apr 2023 13:58:54 GMT

@PrinceAde formats work in PROC MEANS and most data analysis PROCs