Proc tabulate - only show where var is greater than x

Reply
Frequent Contributor
Posts: 76

Proc tabulate - only show where var is greater than x

proc tabulate data=data;

where [VAR>10000];

class sex occupation;

var weightfield;

table occupation , sex*weightfield*(sum);

run;

 

 

Now obviously the VAR>10000 does not work. what am I supposed to put to only show the table with results greater than 10000?

 

Super User
Posts: 7,470

Re: Proc tabulate - only show where var is greater than x

A where condition will be applied on the input dataset and prevent observations not fitting the condition from being included in the calculation.

To work on the result, I'd recommend creating a dataset with the result and applying a where= dataset option there.

Brackets of any type are not necessary around conditions in where statements. And the square brackets have no function in SAS apart from arrays and only cause a syntax error here.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 76

Re: Proc tabulate - only show where var is greater than x

I only put the square brackets to emphasise it was a placeholder statement.

 

So there's no way to do this in a proc tabulate??? Seems silly to recreate an entire database just to make a small table. 

Super User
Posts: 7,470

Re: Proc tabulate - only show where var is greater than x

As shown in @ballardw's example, the where condition works perfectly. Just be sure that the input dataset has the variable(s) used in the where condition.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,144

Re: Proc tabulate - only show where var is greater than x

proc tabulate data=data;

where VAR>10000;

class sex occupation;

var weightfield;

table occupation , sex*weightfield*(sum);

run;

 

Frequent Contributor
Posts: 76

Re: Proc tabulate - only show where var is greater than x

I already said this does not work.

 

ERROR: Variable Var is not on the file DATA.

Super User
Posts: 7,470

Re: Proc tabulate - only show where var is greater than x


fieldsa83 wrote:

I already said this does not work.

 

ERROR: Variable Var is not on the file DATA.


Well, you need to inspect your data and use only variables present in the input dataset. I thought that is obvious for anyone who has mastered the basics of SAS.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 76

Re: Proc tabulate - only show where var is greater than x

Obviously you don't understand my question.

 

I'm trying to make a table, from a dataset but ONLY show the results if they are greater than 10,000 observations. 

Super User
Posts: 7,470

Re: Proc tabulate - only show where var is greater than x

[ Edited ]

So you want proc tabulate to only create the result if that result has more than 10000 lines?

 

(in SAS speak, observation = dataset record)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 76

Re: Proc tabulate - only show where var is greater than x

No because it's weighted.

Super User
Posts: 7,470

Re: Proc tabulate - only show where var is greater than x

[ Edited ]

So you only want to have lines in the output where the summary result exceeds 10000? In that case, I'd suggest having proc tabulate create an output dataset and applying the where option there.

Like

proc tabulate data=sashelp.class out=test (where=(weight_sum>100));
var weight;
class sex age;
table sex,age * weight;
run;

proc tabulate data=test;
var weight_sum;
class sex age;
table sex,age * weight_sum;
run;

The second tabulate is just there to format the dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,144

Re: Proc tabulate - only show where var is greater than x


fieldsa83 wrote:

Obviously you don't understand my question.

 

I'm trying to make a table, from a dataset but ONLY show the results if they are greater than 10,000 observations. 


I'll bite: How do you know there are greater than 10,000 observations?

 

Note that your original questions was: "Now obviously the VAR>10000 does not work. what am I supposed to put to only show the table with results greater than 10000?" 

Which is significantly different than observations. "results" can have an awful lot of meanings but you did not communicate your use very clearly.

 

You would have to check the number of observations PRIOR to the proc tabulate as there isn't anything I'm aware of in most where clauses that could indicate the numbers of observations available. And even then you potentially need to check the specific values as Tabulate will discard records with missing values of class variables.

 

 

Super User
Posts: 5,372

Re: Proc tabulate - only show where var is greater than x

You can do this if you create your own format:

 

proc format;

value blank low-<1000=' ' other=[best8.];

run;

 

If you'd like, you could use some other character such as an asterisk, for values that are too low.  Then apply the format in PROC TABULATE:

 

table occupation, sex*weightfield*sum*f=blank.;

 

This code is untested, but it should work.

Ask a Question
Discussion stats
  • 12 replies
  • 303 views
  • 3 likes
  • 4 in conversation