2 users online | 2 Guests and 0 Registered

Can I export a dataset to a CSV file?


There are several ways to do this.  This utility macro allows you to specify various paramaters, and validates these before generating the output file.

%macro ds2csv ( csvfile  = 
               ,csvfref  =
               ,openmode = replace
               ,colhead  = y
               ,data     = &syslast
               ,formats  = y
               ,labels   = y
               ,sepchar  =
               ,var      =
               ,where    =
               ) ;
  options nomprint nomlogic nosymbolgen nonotes ;
  options noquotelenmax ; /*** Suppress WARNING message about quoted string longer than 262 chars ***/
  %local i usecolhead useformats uselabels s itthey isare ;
  %*** Validate Parameters *** ;
  %if &csvfile ne and &csvfref ne %then
  %do ;
    %put WARNING: CSVFILE and CSVFREF have both been specified. CSVFREF parameter will be ignored. ;
    %let csvfref = ;
  %end ;
    
  %if &csvfref ne %then
  %do ;
    proc sql noprint ;
      select fileref into :extfiles separated by ' '
      from dictionary.extfiles
      ;
    quit ;
    %if %sysfunc(find(&extfiles,&csvfref,i)) = 0 %then
    %do ;
      %put ERROR: The FILEREF %upcase(&csvfref) has not been assigned. No records will be written. ;
      %goto endmac ;
    %end ;
  %end ;
 
  %let openmode = %lowcase(&openmode) ;
  %if &openmode ne replace and &openmode ne append %then
    %do ;
      %put ERROR: Invalid OPENMODE parameter specified.  Valid options are REPLACE, APPEND. CSV file will not be created. ;
      %goto endmac ;
    %end ;
    
  %let colhead = %lowcase(%substr(&colhead,1,1)) ;
  %if &colhead ne y and &colhead ne n %then
  %do ;
    %put ERROR: Invalid COLHEAD parameter specified.  Valid options are Y, N. CSV file will not be created. ;
    %goto endmac ;
  %end ;
    %else %if &colhead = y %then %let usecolhead = yes ;
    %else %let usecolhead = no ;
 
  %let formats = %lowcase(%substr(&formats,1,1)) ;
  %if &formats ne y and &formats ne n %then
  %do ;
    %put ERROR: Invalid FORMATS parameter specified.  Valid options are Y, N. CSV file will not be created. ;
    %goto endmac ;
  %end ;
    %else %if &formats = n %then %let useformats = noformats ;
    
  %let labels = %lowcase(%substr(&labels,1,1)) ;
  %if &labels ne y and &labels ne n %then
  %do ;
    %put ERROR: Invalid LABELS parameter specified.  Valid options are Y, N. CSV file will not be created. ;
    %goto endmac ;
  %end ;
    %else %if &labels = y %then %let uselabels = label ;
 
  %if &data = _NULL_ %then
  %do ;
    %put ERROR: No dataset specified. CSV file will not be created. ;
    %goto endmac ;
  %end ;
 
  proc sql noprint ;
    select distinct libname into :all_libs separated by ' '
    from dictionary.libnames
    ;
  quit ;
  %if %sysfunc(find(&data,.)) > 0 %then
  %do ;
    %let lib = %upcase(%scan(&data,1,.)) ;
    %let dsn = %upcase(%scan(&data,2,.)) ;
    
    %if %sysfunc(find(&all_libs,&lib,i)) = 0 %then
    %do ;
      %put ERROR: The LIBREF &lib has not been assigned. CSV file will not be created. ;
      %goto endmac ;
    %end ;
    
    proc sql noprint ;
      select memname into :all_dsns separated by ' '
      from dictionary.tables
      ;
    quit ;
    %if %sysfunc(find(&all_dsns,&dsn,i)) = 0 %then
    %do ;
      %put ERROR: The dataset &dsn is not present in the library &lib. CSV file will not be created. ;
      %goto endmac ;
    %end ;
  %end ;
  %else
  %do ;
    %if %sysfunc(find(&all_libs,%str( USER ),i)) > 0 %then %let lib = USER ;
      %else %let lib = WORK ;
    %let dsn = %upcase(&data) ;
  %end ;
 
  %if &csvfile = and &csvfref = %then
  %do ;
    %let csvfile = %lowcase(&dsn).csv ;
  %end ;

  %let hexvals = ;
  data _null_ ;
    do i = 0 to 255 ;
      call symputx('hexvals', catx(' ',symget('hexvals'),put(i,hex2.))) ;
    end ;
  run ;
  %let sepchar = %upcase(&sepchar) ;
  %if %sysfunc(find(&hexvals,&sepchar,i)) = 0 %then
  %do ;
    %put WARNING: The SEPCHAR parameter &sepchar is not a valid hexadecimal character. A comma-separated file will be generated. ;
    %if &sysscp = WIN %then %let sepchar = 2C ;
      %else %let sepchar = 6B ;
  %end ;
  %let act_sep = %sysfunc(inputc(&sepchar,$hex2.)) ;
 
  %let invalid_vars = ;
  %let valid_vars   = ;
  %let comma_vars   = ;
  %if &var ne %then
  %do ;
    %let varcount = %eval(%sysfunc(count(&var,%str( ))) + 1) ;
    proc sql noprint ;
      select name into :all_vars separated by ' '
      from dictionary.columns
      where libname = "&lib"
        and memname = "&dsn"
        ;
    quit ;
    %do i = 1 %to &varcount ;
      %let ivar = %scan(&var,&i) ;
      %if %sysfunc(find(&all_vars,&ivar,i)) > 0 %then
        %do ;
          %let valid_vars = &valid_vars &ivar ;
          %let comma_vars = &comma_vars &ivar %sysfunc(ifc(&i ne &varcount,%str(&act_sep),%str())) ;
        %end ;
        %else %let invalid_vars = &invalid_vars &ivar ;
    %end ;
    %if &invalid_vars ne %then
    %do ;
      %if %sysfunc(countc(&invalid_vars,%str( ))) = 0 %then
        %do ;
          %let s      =    ;
          %let itthey = It ;
          %let isare  = is ;
        %end ;
        %else
        %do ;
          %let s     = s    ;
          %let ithey = They ;
          %let isare = are  ;
        %end ;
      %put WARNING: The variable&s: %upcase(&invalid_vars) &isare not present in the dataset &lib..&dsn.. &itthey will be omitted. ;
      %put WARNING: Only the variables: %upcase(&valid_vars) will be written to the file. ;
    %end ;
  %end ;
  %else
  %do ;
    proc sql noprint ;
      select name into :comma_vars separated by "&act_sep "
      from dictionary.columns
      where libname = "&lib"
      and memname = "&dsn"
      ;
    quit ;
    %let valid_vars = %sysfunc(compress(%bquote(&comma_vars),%str(%bquote(&act_sep)))) ;
  %end ;
 
  proc sql noprint ;
  select sum(length) into :totlen
  from dictionary.columns
  where libname = "&lib"
    and memname = "&dsn"
    ;
  select nobs into :totobs
  from dictionary.tables
  where libname = "&lib"
    and memname = "&dsn"
    ;
  quit ;
  %if %eval(%sysfunc(length(%bquote(&comma_vars)))+2>&totlen) %then %let totlen  = %eval(%sysfunc(length(%bquote(&comma_vars)))+2) ;
  %if &colhead = y %then %let totobs = %eval(&totobs + 1) ;

  /***
  data _null_ ;
    set &lib..&dsn ;
      file
      %if &csvfile ne %then "&csvfile" ;
      %else &csvfref ;
      dsd dlm = "&sepchar"x lrecl = &totlen &repmod ;
      %if &colhead = y %then
      %do ;
        if _n_ = 1 then put "&comma_vars" ;
      %end ;
      put &valid_vars ;
  run ;
  ***/
 
  proc export data    = &lib..&dsn (keep = &valid_vars
                                    %if &where ne %then
                                    where = (&where)
                                    ;
                                   )
              outfile = %if &csvfile ne %then "&csvfile" ;
                          %else &csvfref ;
              dbms    = dlm
              &useformats
              &uselabels
              &openmode
              ;
    
  delimiter = "&sepchar"x ;
  putnames  = &usecolhead ;
  run ;
 
  options notes ;
  %put NOTE: &totobs records have been written to the file: &csvfref&csvfile..  ;
  %put NOTE: The record separator is &sepchar:    &act_sep ;
%endmac:
%mend ds2csv ;

Attached files: ds2csv.sas

Author:
Alan D Rudland
Revision:
1.0
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category

Tags