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.
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. ;
%if &byvar. = %then %do ;
%put %str(E)RROR: the BYVAR = macro variable was not supplied. ;
%let header = %upcase( %substr( &header., 1, 1 ) ) ;
%if %index( &dsn., . ) %then %do ;
%let libname = %scan( &dsn., 1 ) ;
%let memname = %scan( &dsn., 2 ) ;
%else %do ;
%let libname = work ;
%let memname = &dsn. ;
proc sql noprint ;
into :names separated by ","
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. ;
create table temp_&memname. as
order by &byvar. ;
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_ ) ( : ) ;
proc delete data = temp_&memname. ;
If you have programmed with SAS in the last 15 years, you have probably had a reason to share your SAS results in PDF format. The ODS PDF destination, much like a well-designed car, has evolved over the years, offering progressively nicer features like security, enhanced image formatting and embedded […]
The post ODS PDF destination in SAS 9.4: Come take a look under the hood! appeared first on SAS Users.Read More
When you weren't watching, SAS did it again. We smuggled Yet Another Excel Engine into a SAS release. SAS 9.2 Maintenance 2 added the XLSX engine, which allows you to read and write Microsoft Excel files as if they were data sets in a library. The big advantage of using […]Read More
When I was an undergraduate physic major, my favorite professor would start each class with a joke or pun. One day he began class with a paraphrase of a famous quote from the movie Star Trek 4: The Voyage Home (the one with the whales). "Today," my professor said, imitating […]Read More
Mount St. Helens volcano here in the US had a big eruption 35 years ago this week! Do you know exactly where it is located? Perhaps this SAS map can help... As you might have guessed, I'm a big fan of the awesome power of nature (hurricanes, tornadoes, lightning, earthquakes, and […]Read More