split DSN to CSV

The below macro can be used to split a SAS data set into comma separated value (CSV) files based on the &byvar macro variable. The macro parameters support the option of including or ignoring the header row.

After some initial parameter verification, the dictionary.columns table is used to obtain the name of columns in the correct order. The input data set is then sorted on the &byvar macro variable to a new temp_&memname data set.

A data _NULL_ (do not create a SAS data set) step creates the output files via the use of the filevar= option. The filevar=variable (character string containing the physical filename) is used to dynamically specify the file used by the put statement. Interestingly, the filevar = variable is not written to the output file rather it is treated like other SAS automatic variables such as _N_ or _ERROR_.

The delimiter sensitive data (DSD) option uses a comma ( , ) as the default delimiter, so there is no need to specify a DLM= value. The put ( _ALL_ ) ( : ) is a formatted output statement [syntax is ( var list ) ( format list )] that writes out all non automatic variables to the output file. You could add the _N_ automatic variable such as put ( _N_ _ALL_ ) ( : ) if you wanted to show the row number. Just be sure to also alter the header and understand that _N_ does not get reset to zero when changing by groups. The last step is to clean up (delete) the temporary data set via proc delete.

%macro splitdsntocsv
  ( 
      dsn          = sashelp.class
    , byvar        = sex
    , outputprefix = c:\temp\temp_
    , outputsuffix = csv
    , header       = Y 
  ) ;

  %local libname memname names ;

  %if %sysfunc( exist( &dsn. ) ) = 0 %then %do ;
    %put %str(E)RROR: the DSN = &dsn. does not exist. ;
    %return ;
  %end ;

  %if &byvar. = %then %do ;
    %put %str(E)RROR: the BYVAR = macro variable was not supplied. ;
    %return ;
  %end ;

  %let header = %upcase( %substr( &header., 1, 1 ) ) ;

  %if %index( &dsn., . ) %then %do ;
    %let libname = %scan( &dsn., 1 ) ;
    %let memname = %scan( &dsn., 2 ) ;
  %end ;
  %else %do ;
    %let libname = work ;
    %let memname = &dsn. ;
  %end ;

  proc sql noprint ;
    select         name
      into         :names separated by ","
      from         dictionary.columns
      where            libname = "%upcase( &libname. )"
                   and memname = "%upcase( &memname. )"
      order by     varnum ;     

    %if not %sysfunc( find( "&names.", &byvar., i ) ) %then %do ;
      %put %str(E)RROR: The BYVAR = &byvar. does not exist. ;
      quit ;
      %return ;
    %end ; 

    create table temp_&memname. as
      select       *
        from       &dsn.
        order by   &byvar. ;
  quit ;

  data _null_  ;
    set temp_&memname. ;
      by &byvar. ;
         
    temp_var = cats( "&outputprefix.", &byvar., ".", "&outputsuffix." ) ;
    file tempfref dsd lrecl = 32767 filevar = temp_var ;

    if first.&byvar. and "&header." = "Y" then put "&names." ;
    put ( _all_ ) ( : ) ;
  run ;

  proc delete data = temp_&memname. ;
  run ;
%mend ;