topic Re: Write a line to a file that exceeds 32,767 characters in SAS Programming

Write a line to a file that exceeds 32,767 characters

Zelazny7 — Wed, 26 Mar 2014 17:21:55 GMT

I am trying to output a dataset to a csv flat file. My header record exceeds the 32,767 character limit for a put statement. How can I output a file that contains a header record longer than the lrecl max? The code below writes the header and inserts a newline once the lrecl limit has been reached.

/* Dummy data */

%macro mk_dummy();

data TEST;

%let i = 1;

%do %until(&i = 2000);

MY_LONG_VARIABLE_NAME_&i = "1";

%let i = %eval(&i + 1);

%end;

output;

run;

%mend

%mk_dummy()

/* Set some macro values */

%let DSET = TEST;

%let fileloc=%str(../data/test.csv);

/* Run proc contents and extract the variable number and name */

proc contents data=&dset

out=__HEADER_VARS__(keep = VARNUM NAME)

noprint;

run;

proc sort data=__HEADER_VARS__ ; by VARNUM; run;

/* Store each variable name into its own macro variable */

data _NULL_;

set __HEADER_VARS__ end=eof;

call symput(compress(cats("VAR",_N_)), NAME);

if eof then call symput("NVARS", _N_);

run;

/* loop over the macro variables and write the header record, then write the file */

%macro mk_file();

data _NULL_;

file "&FILELOC" LINESIZE=32767 DLM=",";

if _N_ = 1 then do;

%let i = 1;

%do %until(&i > &NVARS);

%if &i = 1 %then %do;

put "%trim(&&VAR&i)," @;

%end;

%else %if &i = &NVARS %then %do;

put "%trim(&&VAR&i)";

%end;

%else %do;

put "%trim(&&VAR&i)," @;

%end;

%let i = %eval(&i + 1);

%end;

;

end;

set &DSET;

put (_ALL_)(:);

format _CHARACTER_ $quote200.;

run;

%mend;

%mk_file

Re: Write a line to a file that exceeds 32,767 characters

data_null__ — Wed, 26 Mar 2014 18:07:43 GMT

Do it a different way.

data test;
   array MY_LONG_VARIABLE_NAME_[2000] $1; 
   retain MY_LONG_VARIABLE_NAME_ '3'; 
   output; 
   output; 
   run; 
filename FT66F001 temp lrecl=100000; 
data _null_; 
   set test;
   file FT66F001 dsd; 
   if _n_ eq 1 then link names;
   put (_all_)(~);
   return; 
names:
   length _name_ $32; 
   do while(1);
      call vnext(_name_);
      if upcase(_name_) eq '_NAME_' then leave;
      put _name_ ~ @;
      end; 
   put; 
   return; 
   run; 

Re: Write a line to a file that exceeds 32,767 characters

Zelazny7 — Wed, 26 Mar 2014 20:48:56 GMT

What sorcery is this!? It worked fantastically. I've never seen the "link" pattern before. Very cool.

Re: Write a line to a file that exceeds 32,767 characters

data_null__ — Thu, 27 Mar 2014 13:16:05 GMT

The link is used in this program to keep _NAME_ unknown as the program is compiled, so that when PUT (_ALL_) (~); is compiled the _ALL_ "SAS variable list" contains only the variables from the data set named on the SET statement.

Also the parenthesis around _ALL_ in the PUT statement are important to make _ALL_ a variable list and not the _ALL_ put statement directive. In other words there is a difference between

put _all_;

and

put (_all_)(=);

although they produce almost identical output.