BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ciro
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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

6 REPLIES 6
Damo
SAS Employee

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Jade | Level 19

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
Quartz | Level 8

unfortunately  do not have the variables formatted.

however great job. thank you very much.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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