DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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

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


Accepted Solutions
Solution
‎01-03-2018 09:53 AM
Super Contributor
Posts: 500

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

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;

View solution in original post


All Replies
SAS Employee
Posts: 199

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

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:

export.png

 

Hope that helps.


Cheers,
Damo

Super User
Super User
Posts: 9,213

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

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

Contributor
Posts: 66

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

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)

 

 

 

 

 

 

 

Super User
Super User
Posts: 9,213

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

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.

Solution
‎01-03-2018 09:53 AM
Super Contributor
Posts: 500

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

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;
Highlighted
Contributor
Posts: 66

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

Posted in reply to andreas_lds

unfortunately  do not have the variables formatted.

however great job. thank you very much.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 204 views
  • 0 likes
  • 4 in conversation