Date:         Wed, 5 Jul 2006 15:03:31 -0400
Reply-To:     Peter Crawford <peter.crawford@BLUEYONDER.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <peter.crawford@BLUEYONDER.CO.UK>
Subject:      Re: Create dataset names from variable values
Comments: To: Donal KElly <donal.kelly@CSO.IE>

On Wed, 5 Jul 2006 03:41:15 -0400, Donal Kelly <donal.kelly@CSO.IE> wrote:

>Hi,
>
>I am trying to split a dataset into 20 individual files and assign a
>dataset name based on the value of a certain variable within each dataset
>(see sample code below). I can split the file easily enough by looping
>through the Region ids, but I can't figure out how to pass the value of
>Region for each file to the dataset name.
>
>I've been trying to pass the value of Region to a macro variable but with
>no success.
>
>ID Region Reg_ID
>01 North   1
>02 North   1
>03 South   2
>04 South   2
>05 East    3
>06 East    3
>07 West    4
>08 West    4
>
>Outcome > North.sas7bdat, South.sas7bdat etc. .
>
>I would appreciate any suggestions that are out there.
>Donal


If the spec is clear and simple then I think, so the code should be
too !

Without using any new macros, the demo below uses :
  proc summary for a list of the unique values of the "by-var",
  proc sql delivers,
    a list of the required output table names in &names
    syntax to select obs to appropriate output, in &logic
  just 1 data step, to deliver it all

The following uses sashelp.class as a demo, but, using macro vars
it could point to anything

NOTE: the code does not add new mesages.
  imagine the error messages for using a numeric by-var
  imagine the error messages if the by-var doesn't exist !


*****************************************************
* gen  tables by value                              *
****************************************************;

%let wanted= sashelp.class ; * your original;
%let  byvar= sex ;           * split on this by var ;

proc summary data=&wanted noprint nway ;
  class &byvar ;
  output ;
run;
proc sql noprint;
 select 'work.' !! &byvar
      , "if &byvar =" !! quote( &byvar )
           !! ' then output work.'
           !! &byvar
   into :names separated by ' '
      , :logic separated by '; else '
   from _last_
       ;
quit;
option symbolgen;
data &names ;
  set &wanted ;
  &logic ;
run;

If you must deliver in some other library, just change that
 'work.' in the syntax above

Further testing :   just change the byvar to   name

It works.

Peter