proc tabulate out= conditions for out dataset

[Daniela_P]

Dear experts,

I'd like to create a dataset resulting from the out of a proc tabulate only if the minimum of the frequency variable in it exceeds a limit.

That's to say, for example:

if var_sum > 3000 then out the dataset; else do not.

Any advice to do it?

Thank you all.

Daniela

[RW9]

Following the guidance you will find by the Post button on new questions, i.e. post test data in the form of a datastep, what the output should look like etc., will get you more accurate answers.  As such I am just guessing here:

proc tabulate data=have out=want (where=(var_sum > 3000));
...
run;

That might work.  Alternatively just have a datastep after the tabulate which checks this.  

[Daniela_P]

Thank you very much. It worked perfectly!

[Daniela_P]

Unfortunately the output returns only the rows satisfaying the condition... instead I'd like the output not to be saved at all if the condition isn't satisfied.
To put a control on the ds output, instead, how could be possible to delete a ds if the minimum of a variable exceeds a value?
Thanks

[Cynthia_sas]

Hi:

  It is not clear to me what you want and without data, it is hard to visualize. By default, WHERE operates at the row level. For example, SASHELP.CLASS has 6 values for the AGE variable, so by default, OUT= without a WHERE will output 6 observations, as shown below:

However, since the WHERE operates at the ROW or OBSERVATION level, it is selecting the rows to go into the final OUT= dataset. These 3 examples show Condition A, Condition B and Condition C -- note that A and B have different number of ROWS from the default.

Finally, when NONE of the rows satisfy CONDITION C we find that WORK.CONDC has 0 observations.

  You said that you'd like the output not to be saved at all if the condition isn't satisfied.How does that apply to the above 3 conditions? You would not want any of them saved/created or you would only want the condition C file not to be created? Either way, you might have to create the file in work and then test after the fact (after TABULATE is finished) whether it contains what you want. I would envision this type of data-specific file saving or deleting to involve some kind of macro program to check the contents of the OUT= file.

cynthia

[Daniela_P]

Dear Cynthia,

I'll try to explain better... let's make an example close to yours:

I'd like to create the file only if height_sum gt 150 but I want to be certain the every value in the table (of height_sum) is gt 150:

I do not want to select, in the table, values of height_sum gt 150 and delete all other values.

I want to keep every value gt 150 only if every value in height_sum is gt 150.

If there's at least one value lt 150 I don't want the table to be created at all.

I'm trying to use this code referring to the table created in the output with no conditions:

proc tabulate data=sashelp.class out=default;
class age;
var height;
table age,
	  height*sum height*mean;
run;

%macro del_(ds,Var,n);
 proc sql noprint;
 select sum(&Var<&n) into :ctrl from &ds; 
 %if ctrl >0 %then drop table &ds;; 
 quit;
%mend del_;

With the tabulate above I obtain this ds (here is the proc print of the ds work.defalut created by the tabulate):

                                             Height_    Height_
Oss    Age    _TYPE_    _PAGE_    _TABLE_      Sum        Mean

 1      11      1          1         1        108.8     54.4000
 2      12      1          1         1        297.2     59.4400
 3      13      1          1         1        184.3     61.4333
 4      14      1          1         1        259.6     64.9000
 5      15      1          1         1        262.5     65.6250
 6      16      1          1         1         72.0     72.0000

%del_(default,height_sum,150);/*ok, table not saved (work.default is deleted)*/

In the case n=150 I'd like the table not to be saved, because in height_sum there are two values < 150 (Oss number 1 and Oss number 6): that's what sas correctly does!

If I change the value n in the macro from 150 to 70 I'd like the table to be saved (because no value in height_sum is <70) but unfortunately it doesn't: in any case sas deletes the table...

%del_(default,height_sum,70);/*KO, table not saved (work.default is deleted) --this is not what I want--*/

I'm surely making some mistakes in the code, but I can't find where (I don't know weel proc sql..).

Many thanks in advance

D

[RW9]

Can I suggest that we start again here as this seems to be going round and round.  Start by following the guidance you will have seen when you posted the question.  Post test data - in the form of a datastep in a code block (its the {i} above the post area.  Next post an example of how the output should look like.  Once we have an exact example of what goes in and what should come out then we can look at ways to get there.  from what I hear in this post, tabulate probably isn't the way to go, a datastep with some retained sums ossibly would.  Also, a question, why would you want no output of any kind for certain criteria - it may occur that you then get nothing at all.  

[Daniela_P]

I edited my last post according to your advices. I hope to be been clearer now...
Answering to your question, I'd like no output for the criteria I choose beacause I have to produce about 100 ds of output and I want to discard those where the intersections do not exceed a certain threshold.
Thanks,
D