Date:         Tue, 8 Jul 1997 12:34:36 -0400
Reply-To:     Tra <Tra@PROTEUS.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Tra <Tra@PROTEUS.CO.UK>
Subject:      SASTip - getting number of levels of a variable

     I often use SQL code to set macro variables.  In a recent program I
     wanted to set a macro variable to the number of distinct levels of a
     variable (which was later used in BY processing).  This can be done by
     using discrete in the SQL query and then accessing &sqlobs to get 'the
     number of rows processed by an SQL procedure statement'.  This works
     fine if the resulting list of values is printed or saved to an sql
     variable with a 'separated by' clause.  Otherwise it usually sets
     SQLOBS to 1.  I assume this is due to an SQL optimization, that if
     nobody wants to see the results then stop after the first one.
     However, it caught me out, so I thought I would share the experience
     with the list.
 
     Here is a short example:
 
     data test;
         input a;
     cards;
     1
     2
     2
     3
     ;
     proc sql noprint;
         select distinct a from test;
         %let nlev1 = &sqlobs;
         select distinct a into :lev separated by ' ' from test;
         %let nlev2 = &sqlobs;
         reset print;
         select distinct a from test;
         %let nlev3 = &sqlobs;
     quit;
     %put nlev1=&nlev1 nlev2=&nlev2 nlev3=&nlev3;
 
     I run version 6.12 on Windows 3.1, which produces the log output:
 
     nlev1=1 nlev2=3 nlev3=3
 
     The desired answer is 3.
 
     Does anybody know a neater way to get the number of levels in a
     dataset?  In SCL one can use the LVARLEVEL or VARLEVEL functions.  I
     have not tried them but I assume that these are not avaialable through
     %sysfunc as they need scl list or array arguments.
 
     Tim Auton
     Proteus Molecular Design Ltd.