topic Re: proc freq table order options in SAS Programming

proc freq table order options

thackkr — Tue, 26 Nov 2019 17:17:41 GMT

VERY new SAS user here 🙂 I'm looking for a way to tell SAS to display table values in the order in which I hard coded them. For example, I created a subset of an existing data set and recoded education level categorical data like this:

if _EDUCAG = 1 then educat = "No HS Diploma";

if _EDUCAG = 2 then educat = "HS Diploma";

if _EDUCAG = 3 then educat = "Some College";

if _EDUCAG = 4 then educat = "College Grad";

if _EDUCAG = 9 then educat = "Don't Know";

When I use proc freq to create frequency and crosstabulation tables, I'd like to know if there is a way to include an ORDER= statement that will display the table data in the order in which I created it (e.g., List "No HS Diploma" first, "HS Diploma" next, etc.). It looks like the default is to list the categories alphabetically.

Thank you!

Re: proc freq table order options

r_behata — Tue, 26 Nov 2019 17:25:02 GMT

Try Formats :

proc format;
	value educat 1="No HS Diploma"
				2="HS Diploma"
				3="Some College"
				4="College Grad"
				9="Don't Know";
run;


data have;
	do _EDUCAG =1,2,3,4,9;
		output;
	end;
run;


proc freq data=have;
	tables _EDUCAG / missing;

	format _EDUCAG educat.;
run;

Re: proc freq table order options

thackkr — Tue, 26 Nov 2019 17:53:19 GMT

Thank you so much for the suggestion! A co-worker suggested I look at formats, too. I'm guessing this means there is no simple way to do it without proc format then? Thank you!

Re: proc freq table order options

braam — Tue, 26 Nov 2019 19:01:46 GMT

What about this? When hard-coding, you may want to consider index.


proc freq data=sashelp.cars;
	where make in ("Acura" "Audi" "BMW");
	tables make;run; 

	data temp; set sashelp.cars;
		if make= "BMW" then NewMake= "1. BMW";
		if make= "Audi" then NewMake= "2. Audi";
		if make= "Acura" then NewMake= "3. Third";
		run;
proc freq data= temp;
	tables NewMake ;run;

Re: proc freq table order options

thackkr — Tue, 26 Nov 2019 19:22:28 GMT

Hadn't thought of that! Thanks so much for the suggestion 🙂

Re: proc freq table order options

ballardw — Tue, 26 Nov 2019 20:59:21 GMT

@thackkr wrote:

Thank you so much for the suggestion! A co-worker suggested I look at formats, too. I'm guessing this means there is no simple way to do it without proc format then? Thank you!

Sort and use ORDER=Data option:

data example;
   do i= 1 to 100;
      _educag = rand('integer',1,5);
      if _educag=5 then _educag=9;
      if _EDUCAG = 1 then educat = "No HS Diploma";
      if _EDUCAG = 2 then educat = "HS Diploma";
      if _EDUCAG = 3 then educat = "Some College";
      if _EDUCAG = 4 then educat = "College Grad";
      if _EDUCAG = 9 then educat = "Don't Know";
      output;
   end;
run;

proc sort data=example;
  by _educag;
run;

proc freq data=example order=data;
   tables educat;
run;

However, if you want to summarize many variables at once it is very unlikely that a sort order would be correct for more than one variable.

IMHO the Format approach is likely the most flexible in the long run. All you need to get a summary of No HS diploma, HS or college and Don't Know is a new format. No additional variables added Also the groups created by formats will generally be used by any analysis or graphing procedure.