BookmarkSubscribeRSS Feed
SannaSanna
Quartz | Level 8

Hi.  

I'm here again looking for help.  This is the best place ever!!  

 

I am trying to run frequency counts for a large group of data by age.  I need to run counts by specific age numbers as well as age groups.  (The Age Groupings are the same ID patients but they are grouped)  Is there a way to run this in one step?   Thank you so much everyone!  Here is a sample of my data: 

ID Age Quad
Smi12 3 N
Cha890 2 N
Tse2987 18 S
Bra7650 22 N
Gon12409 31 N
Rod23433 35 S

 

Here is the output I want: 

Output    
Age Quad Freq
3 N 1
2 N 1
18 S 1
22 N 1
31 N 1
35 S 1
0-5 N 2
6-18 S 1
19-35 N 2
19-35 S 1

 

data re4;
input ID $ Age $ Quad $;
datalines;
Smi12 3 N
Cha890 2 N
Tse2987 18 S
Bra7650 22 N
Gon12409 31 N
Rod23433 35 S
;
run;
proc freq data=re4;
tables age*quad/list missing;run;

4 REPLIES 4
novinosrin
Tourmaline | Level 20

Hi @SannaSanna  It seems what you likely need is Multilabel formats for your grouping. The link-

https://blogs.sas.com/content/sgf/2016/12/16/creating-and-using-multilabel-formats/

 

has all the details for your reference. 

 

Once you have assigned the format labels, you can use the same in a statistical proc. HTH

ballardw
Super User

One way to accomplish such summaries is to use a MULTILABEL format. However, only a few procedures can use one of these formats to display all the levels.

I have some example code that was designed around around a slightly different type of data but can demonstrate the interactions of the format definition (there are three different definitions with the same values in different orders) and the options used to display the values.

Formats are one of the easiest and most flexible ways to create groups that are honored by most procedures, it is just the multiple level ones that are restricted. Nice thing is that the Procedures Report and Tabulate will use them.

The formats I show below are for numeric values but the only differences for character would be that each value on the left of the = would be in quotes and that instead of doing 1 - 5 for a range of numeric values you have to list EACH character value '1', '2','3','4','5' = 'Group text to display'

The example above includes some features related to value displays with indents controlled by the format as well using the 'ASIS' and other style options in the classlev statement.

The format ACCIDENTS below behaves similar to your want.

/* To demonstrate how the order of definition affects appearance in
   multilabel formats. Also appearance options to show the spaces to
   get the indent as desired and fix column widths.
   And investigate whether class level format based style overrides work
with MLF in proc tabulate.  Result: NO.
*/
proc format library=work;
value accidentl (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
4-5 = ' Nontransport accidents'
5 = '   Fishing'
;
value accidentr (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
4-5 = ' Nontransport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
5 = '   Fishing'
;
value accidents (multilabel notsorted)
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
5 = '   Fishing'
1-5 = 'Accidents'
1-3 = ' Transport accidents'
4-5 = ' Nontransport accidents'
;
value mf
1 = "Male"
2 = "Female"
;

value accsimple
1 = 'Motor vehicle accidents'
2 = 'Water, air, and space'
3 = 'Other land transport accidents'
4 = 'Nontransport accidents'
5 = 'Fishing'
;
run;

/* populate a dataset to display */
/* This specifically does NOT generate any data for FISHING above*/
/* to display the behavior of the options below in those cases. */
data junk; 
   do i=1 to 50;
      type = round(4*ranuni(1234)+.5);
      sex = round(2*ranuni(3455)+.5);
      output;
   end; 
run;


/* Notice that before we get here the data is NOT sorted */
/* in any manner!!!! */
title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl';
proc tabulate data=junk order=data ;
   class type / mlf PRELOADFMT;
   /*ASIS preserves the leading spaces in the format, Cellwidth here is optional
     for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF
     not the OUTPUT window
  */
   classlev type/ style=[asis=on cellwidth=1.5in];
   class sex;
   /* the formatted values of SEX normally take up different lengths, this fixes
      the column widths to be the same for both headers. May cause interesting
      appearances with large numbers of displayed digits if requested in formats*/
   classlev sex/ style=[cellwidth=.5in];
   /* to get the ALL to have the same width as SEX need to specify this way*/
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0' row=float;
   format type accidentl. sex mf.;
run;title;

title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentr';
proc tabulate data=junk order=data ;
   class type / mlf PRELOADFMT;
   /*ASIS preserves the leading spaces in the format, Cellwidth here is optional
     for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF
     not the OUTPUT window
  */
   classlev type/ style=[asis=on cellwidth=1.5in];
   class sex;
   /* the formatted values of SEX normally take up different lengths, this fixes
      the column widths to be the same for both headers. May cause interesting
      appearances with large numbers of displayed digits if requested in formats*/
   classlev sex/ style=[cellwidth=.5in];
   /* to get the ALL to have the same width as SEX need to specify this way*/
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0' row=float;
   format type accidentr. sex mf.;
run;title;

title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidents';
proc tabulate data=junk order=data ;
   class type / mlf PRELOADFMT;
   /*ASIS preserves the leading spaces in the format, Cellwidth here is optional
     for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF
     not the OUTPUT window
  */
   classlev type/ style=[asis=on cellwidth=1.5in];
   class sex;
   /* the formatted values of SEX normally take up different lengths, this fixes
      the column widths to be the same for both headers. May cause interesting
      appearances with large numbers of displayed digits if requested in formats*/
   classlev sex/ style=[cellwidth=.5in];
   /* to get the ALL to have the same width as SEX need to specify this way*/
   table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
   / printmiss misstext='0' row=float;
   format type accidents. sex mf.;
run;title;

Important elements:

1) you have to indicate that the format is multilabel, that would be the MLF option in a Class statement for Proc Tabulate or Define statement for Proc report.

2) you have to associate the format with the variable with a Format statement in Proc Tabulate , Define statement in Proc Report or permanently in a data step.

3) the ORDER option of the procedure interacts with the format definition

4) if you want to show the category when there might not be any data the option PRELOADFMT is used but will require something like the PRINTMISS option to display a category when the value is missing.

 

I strongly question having age as character value as getting sort order output is very screwy. 11 is less than 2 in character values and likely to make a funny looking output table.

Astounding
PROC Star

This should be possible, with a relatively simple program.  Just a few warnings.  First, you have to fix your data.  There is no reason to define AGE as a character variable.  Make it numeric, so it will be possible to define a numeric range like 6-18.  Second, this program is untested.  So you will need to test and see how you like the results.  Finally, there will be some minor changes to the format of the final table.  It can be made to look more like what you requested, since PROC TABULATE has a gazillion tools available to help format its output.

 

At any rate, this ought to work.  After fixing the data, create a simple (not multilabel) format:

proc format;
value grouping 
0-5=' 0-5'
6-18=' 6-18'
19-35='19-35'
;
run;

Then use the format when creating the table:

proc tabulate data=have;
class age quad;
tables age*quad age*f=grouping.*quad;
run;

You may prefer slight changes to the output, so try this and see:

proc tabulate data=have;
class age quad;
tables age age*f=grouping., quad;
run;

As long as this looks like you're heading in the right direction, small formatting issues can be fixed.

 

If you encounter any syntax errors along the way, report back on what they were.  Those can be fixed too.

PGStats
Opal | Level 21

For example:

data have;
input ID $ Age Quad $;
datalines;
Smi12   3   N
Cha890  2   N
Tse2987     18  S
Bra7650     22  N
Gon12409    31  N
Rod23433    35  S
;

proc format;
value multiAge (multilabel notsorted)
0-999 = [7.0]
0-5   = "   0-5"
6-18  = "   6-18"
19-35 = "  19-35";
run;

proc summary data=have nway;
format age multiAge.;
class age / mlf order=formatted;
class quad;
var age;
output out=want n=freq;
run;

proc print noobs data=want; var age quad freq; run;

image.png

 

PG

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

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
  • 4 replies
  • 715 views
  • 2 likes
  • 5 in conversation