Use descriptives statistics values in a sql table filter

[nicnad]

Hi,

I have the following descriptive statitics that I run on the variable Calculation1 in SAS EG 4.1 :

/* -------------------------------------------------------------------
   Code généré par une tâche SAS

   Généré le : 19 février 2013 à 17:50:32
   Par tâche : Statistiques descriptives

   Données en entrée : SASUSER.QUERY_FOR_QUERY1_FOR_PPVS_SCEN3
   Serveur :  SASMain
   ------------------------------------------------------------------- */

PROC SQL;
%_SASTASK_DROPDS(WORK.SORTTempTableSorted);
QUIT;

/* -------------------------------------------------------------------
   Il est inutile de trier la table SASUSER.QUERY_FOR_QUERY1_FOR_PPVS_SCEN3.
   ------------------------------------------------------------------- */
PROC SQL;
CREATE VIEW WORK.SORTTempTableSorted
  AS SELECT Calculation1 FROM SASUSER.QUERY_FOR_QUERY1_FOR_PPVS_SCEN3;
QUIT;
/* -------------------------------------------------------------------
   Exécuter la procédure Means
   ------------------------------------------------------------------- */
TITLE;
TITLE1 "Statistiques descriptives";
TITLE2 "Résultats";
FOOTNOTE;
FOOTNOTE1 "Généré par le Système SAS (&_SASSERVERNAME, &SYSSCPL) le %SYSFUNC(DATE(), FRADFDE9.) à %SYSFUNC(TIME(), TIME6.)";
PROC MEANS DATA=WORK.SORTTempTableSorted
FW=12
PRINTALLTYPES
CHARTYPE
VARDEF=DF

  MEAN
  STD
  MIN
  MAX
  N ;
VAR Calculation1;

RUN;
/* -------------------------------------------------------------------
   Fin du code de la tâche.
   ------------------------------------------------------------------- */
RUN; QUIT;
PROC SQL;
%_SASTASK_DROPDS(WORK.SORTTempTableSorted);
QUIT;

TITLE; FOOTNOTE;

I then create an sql table using the following code :

PROC SQL;

CREATE TABLE SASUSER.FINAL AS SELECT QUERY_FOR_QUERY1_FOR_PPVS_SCEN3.CUSTOMER_ID,

WHERE QUERY_FOR_QUERY1_FOR_PPVS_SCEN3.Calculation1 >= 5.1554977; /* THIS NUMBER IS THE MEAN PLUS TWO TIMES THE STD DEVIATION */

QUIT;

In fact, I want to calculate the mean and std dev for all the records of variable Calculation1 in table QUERY_FOR_QUERY1_FOR_PPVS_SCEN3 and use this value as a filter in my proc sql. How do I do that automatically (having a dynamic variable)?

Thank you for your help and time.

[Reeza]

This is probably better posted in the EG forum.

Regardless, what I'd do is

1. Forget proc means and use a single query builder

2. In the SQL field add 2 calculated column, one that is mean and one that is the std deviation (Computed Columns/Summarized Column)

3. Add another computed column that is the cutoff = mean+2*STD using advanced expressions

4. Add the other columns you need

5. Filter the summarized data - under filter data

[nicnad]

Thank you very much for the reply.

Exactly what I needed.