Date:         Wed, 9 Jul 1997 15:26:40 -0700
Reply-To:     J Hogstad <jhogstad@SOFTCOM.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         J Hogstad <jhogstad@SOFTCOM.NET>
Subject:      Re: SASTip - getting number of levels of a variable
Content-Type: text/plain; charset=ISO-8859-1

Sorry if this is a re-post, I got an error message with the first try.
 
Tim Auton ( Tra@PROTEUS.CO.UK ) wrote :
 
 >      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.
 
 I found the correct answer can be given with the noprint option if you
added one of two things, either 1) a SEPARATED BY statement or 2) a DROP
TABLE and CREATE TABLE as shown below.
 
One of SQL's macro features I use is SQL's INTO clause, it is a good method
of creating macro lists. These lists can then be accessed later as shown in
proc print at the end of the code section. SAS Communications (1997 1Q p48)
illustrates the use of quoted character macro lists. Note you can't use a
CREATE TABLE or VIEW with the INTO clause.
 
 
 data test;
 input a;
 cards;
 1
 2
 2
 3
 ;
run;
 proc sql noprint;
  select distinct a from test;
  %let nlev1 = &sqlobs;
 
  select distinct a into :maclist2 separated by ' ' from test;
  %let nlev2 = &sqlobs;
  %put maclist2=&maclist2;
 
  reset print;
  select distinct a from test;
  %let nlev3 = &sqlobs;
 
  reset noprint;
  /* first example with noprint with separated by */
  select distinct a into :maclist4 separated by ' ' from test;
  %let nlev4 = &sqlobs;
  %put maclist4=&maclist4;
 
  /* first example with noprint with drop and create table */
  drop table sql_data ;
  create table sql_data as
  select distinct a from test;
  %let nlev5 = &sqlobs;
 
  /* create a comma separated macro list for an IN statement */
  select distinct a into :maclist5 separated by ',' from test;
  %let nlev5 = &sqlobs;
  %put maclist5=&maclist5;
 
 quit;
 
 %put nlev1=&nlev1 nlev2=&nlev2 nlev3=&nlev3 nlev4=&nlev4 ;
 %put macro list from SQL maclist2=&maclist2 ;
 %put macro list from SQL maclist4=&maclist4 ;
 %put macro list from SQL maclist5=&maclist5 ;
 
 data bigfile;
 input b;
 cards;
 6
 1
 5
 3
 4
 2
 2
 ;
 run;
 
 proc print  data=bigfile;
  title1 "Example of using a macro list with an IN statement";
  title2 "to subset data";
  where(b in (&maclist5));
 run;
 
 
Jim Hogstad
e-mail jhogstad@softcom.net