output fixed length and record the position and length of a variable

[ciro]

Hi,

happy new year to everyone.

I have to output a dataset of 100 and more variables to a text file with a fixed length format. Each field should be separated from the previous one with, at least, one blank.

Moreover in another dataset I have to write for each variable at which position it starts and which is the (maximum) length of the field.

I would like to do it programmatically.

I imagine that I have to use the output of a proc contents and a, data-driven, data _null_ step, but I am not very good with it.

Any help is appreciated.

Thank you very much in advance

[Damo]

Hi @ciro

Not sure if that will answer all your requirements but did you have a look at %FLATFILE, and Make Your Life Easier, available from 

  ftp://ftp.sas.com/techsup/download/misc/flatfile.pdf

Export of sashelp.cars looks like this:

Hope that helps.

Cheers,
Damo

[RW9]

It should be quite simple.

data _null_;
  set data.vcolumns (where=(libname="WORK" and memname="YOURDATA")) end=last;
  if _n_=1 then call execute('data _null_; file "yourfile.txt"; set work.yourdata; put ');
  call execute(cat(' ',name,' ',cats(type,length,'. " "'));
  if last then call execute(';run;');
run;

Note, not tested

[ciro]

Your code gives the following error.

num8. " "
-----
48
ERROR 48-59: The format NUM was not found or could not be loaded.

I substituted as in the following:

data have;
a='abc'; b=1.23; c='xyzy'; output;
a='ac'; b=11.23; c='xy'; output;
a='ab'; b=111.23; c='x'; output;
a='a'; b=1.2; c='zyz'; output;
a='a'; b=1; c=' '; output;
run;

data _null_;
set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;
if _n_=1 then call execute('data _null_; file "g:\temp\test.txt"; set work.have; put ');
if type='num' then call execute(cat(' ',name,' ',cats('best',length,'. " "')));
else call execute(cat(' ',name,' ',cats(type,length,'. " "')));
if last then call execute(';run;');
run;

however the result is not what I hoped.

1. the numeric column is not left aligned

2. I would like to know the position and the length of the field for each variable (as I have to document the data file)

[RW9]

Minor change then:

data _null_;
  set data.vcolumns (where=(libname="WORK" and memname="YOURDATA")) end=last;
  if _n_=1 then call execute('data _null_; file "yourfile.txt"; set work.yourdata; put ');
  call execute(cat(' ',name,' ',cats(tranwrd(type,"num",""),length,'. " "'));
  if last then call execute(';run;');
run;

For this: "1. the numeric column is not left aligned", yes it is, a numeric is a combination of length and decimals, if length is 8 and number is 12 then the output string is "      12".  So you would need to strip(put()) the value into a character string with preceeding spaces trimmed off.

For this:

2. I would like to know the position and the length of the field for each variable (as I have to document the data file)

It sounds like you are doing this all back to front.  If you are - correctly so - documenting a transfer, the documentation should be filled in beforehand as the agreement between two parties.  That is then signed off and the sender develops to write program, and receiver the read program using the agreement as the basis.  So you should have the spec before you start coding.  Otherwise a spec is pretty much useless to the end user.

As a note, you will find a more standard format such as CSV or XML far simpler for all parties.

[andreas_lds · Accepted solution]

The following code seems to solve the issue, i really hope that someone can optimize (reduce amount of code) it.

I assume that every numeric variable is formatted. Using the length of variables to determine the starting position is not good idea, because as data changes the programs using the exported dataset will have to be changed to - this can, of course, be automated by using the metadata created during the export. @RW9 already said: using csv will make everything easier on the long run.

%macro WriteDatasetToFile(
   SourceDataset=,
   TargetFile=
);

   /* Get the variables of &SourceDataset */
   %local varList;

   proc sql noprint;
      select Name
         into :varList separated by ' '
         from sashelp.vcolumn
            where catx('.', LibName, MemName) = "%upcase(&SourceDataset.)"
      ;
   quit;

   /* Number of variables in &SourceDataset */
   %local varCount lineLength;
   %let varCount = %sysfunc(countw(&varList, %str( )));

   /* Get starting position and max length of each variable. */
   data work.meta;
      set work.class end= jobDone;

      length 
         VarName $ 32
         MaxLength 8
         StartingPos 8
         i len 8
      ;

      keep VarName MaxLength StartingPos;

      /* Array to keep already found max length of each variable, 
       * using _temporary_ takes care of "retaining" the values. */
      array maxlen [&varCount] _temporary_ (&varCount * 0);

      do i = 1 to countw("&varList", " ");
         VarName = scan("&varList", i, " ");
         /* Assuming that at least every numeric variable has a format attached. */
         len = lengthn(vvaluex(VarName));
       
         if len > maxlen[i] then do;
            maxlen[i] = len;
         end;
      end;

      if jobDone then do;
         StartingPos = 1;

         do i = 1 to countw("&varList", " ");
            VarName = scan("&varList", i, " ");
            MaxLength = maxlen[i];
            output;
            /* +1 for the separating blank */
            StartingPos = StartingPos + MaxLength + 1; 
         end;

         call symputx("lineLength", StartingPos-1);
      end;
   run;

   %local exportProgram;
   %let exportProgram = %sysfunc(pathname(work))\export.sas;

   /* This data-null-step creates another data-null-step saved to &exportProgram */
   data _null_;
      set work.Meta end= jobDone;
      file "&exportProgram." ;

      length buffer $ 1000;
      retain buffer;

      if _n_ = 1 then do;
         put 'data _null_;';
         put 'set work.class;';
         /* lrecl + pad => blanks after the last char */
         put "file '&TargetFile.' lrecl= &lineLength. pad;";
         buffer = 'put';
      end;

      buffer = catx(' ', buffer, cats('@', StartingPos), VarName);

      if jobDone then do;
         put buffer ';';
         put 'run;';
      end;
   run;

   %include "&exportProgram.";

%mend;

data work.class;
   set sashelp.class;

   format
      Age 2.
      Height 5.1
      Weight 5.1
   ;
run;

options mprint;

%WriteDatasetToFile(
   SourceDataset= work.class,
   TargetFile= CHANGE_PATH\class.txt
);

options nomprint;

[ciro]

unfortunately  do not have the variables formatted.

however great job. thank you very much.