topic Keep blanks when exporting to .txt in SAS Enterprise Guide

Keep blanks when exporting to .txt

cat2 — Mon, 27 Feb 2017 15:18:56 GMT

Hi,

(I'm using SAS Enterprise Guide 7.1)

I'm trying to make a txt-file that can be read by a program on my computer.

In order for the program to read the file, all values need to be placed on an exact position within the file.

I have a dataset consisting of a number of character variables, some that have leading blanks and some that have trailing blanks.

proc sql;
create table name as
select
'BI' as inter,
'4F' as type,
'25300   ' as account,
put(round(compress(sum(payment,tax),0.01)*100,z19.)) as amount,
put(intnx('Month' , &now. , 0 , 's'),YYMMDDN8.) as trans_date
from source;

When exporting the dataset to txt-file I need the length of the variables to remain the same, i.e. the txt-file needs to include leading and trailing blanks.

I've tried using proc export, but the blanks are removed.

proc export data=name outfile="c:\name.txt" dbms=dlm replace;
putnames=NO;
delimiter='';
run;

Any idea on how to do that?

Thanks!

Re: Keep blanks when exporting to .txt

RW9 — Mon, 27 Feb 2017 15:25:56 GMT

Use datastep and put:

data _null_;
  file "afile.txt";
  put var1 $10. var2 $20.;
run;

Re: Keep blanks when exporting to .txt

Tom — Mon, 27 Feb 2017 16:13:35 GMT

Use a data step to write the text file. Note that if the data needs to be in fixed locations then you do not want a delimited file.

In general you can use a format to fix how many columns a value takes.

Your example translates into this code:

data _null_;
  file "c:\name.txt" ;
  set source ;
  amount = round(100*sum(payment,tax),1);
  trans_date = intnx('Month' , &now. , 0 , 's') ;
  put 'BI' '4F' '25300   ' amount Z19. trans_date yymmddn8. ;
run;

If you do have character variables with leading spaces you can use the $CHAR format to make sure that SAS preserves the leading spaces.

If your format just has blank columns you can either include string literals in your PUT statement or use cursor movement such as +1 or @12 to move to where on the line you want to write the next value. For example if ACCOUNT was actually coming as a variable and you always wanted the 19 digits of AMOUNT to appear starting in column 13 your put statement might look like:

  put 'BI' '4F' account @13 amount Z19. trans_date yymmddn8. ;

PS. Note that SAS cannot actually store 19 distinct digits in a single floating point number. The mazimum precision is really only about 15 digits. So if your amounts are too large the last few digits could be lost.

Re: Keep blanks when exporting to .txt

rogerjdeangelis — Mon, 27 Feb 2017 17:06:59 GMT

May not exactly solve your problem, but you may be able to enhance it.

Ian Whitlock - Create flatfile with layout

HAVE (sashelp.class)
====================

Up to 40 obs from SASHELP.CLASS total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0
  7    Jane        F      12     59.8       84.5
  8    Janet       F      15     62.5      112.5
  9    Jeffrey     M      13     62.5       84.0
 10    John        M      12     59.0       99.5
 11    Joyce       F      11     51.3       50.5
 12    Judy        F      14     64.3       90.0
 13    Louise      F      12     56.3       77.0
 14    Mary        F      15     66.5      112.0
 15    Philip      M      16     72.0      150.0
 16    Robert      M      12     64.8      128.0
 17    Ronald      M      15     67.0      133.0
 18    Thomas      M      11     57.5       85.0
 19    William     M      15     66.5      112.0


WANT  flatfile with layout)
===========================

Layout for SASHELP.CLASS to d:/txt/class.txt

              Column
Column Name   Type    fmtlen
---------------------------------
NAME          char    $8. +1
SEX           char    $1. +1
AGE           num     best10. +1
HEIGHT        num     best10. +1
WEIGHT        num     best10. +1

d:/txt/class.txt

----|----10---|----20---|----30---|----40---|

Alfred   M         14         69      112.5
Alice    F         13       56.5         84
Barbara  F         13       65.3         98
Carol    F         14       62.8      102.5
Henry    M         14       63.5      102.5
James    M         12       57.3         83
Jane     F         12       59.8       84.5
Janet    F         15       62.5      112.5
Jeffrey  M         13       62.5         84
John     M         12         59       99.5
Joyce    F         11       51.3       50.5
Judy     F         14       64.3         90
Louise   F         12       56.3         77
Mary     F         15       66.5        112
Philip   M         16         72        150
Robert   M         12       64.8        128
Ronald   M         15         67        133
Thomas   M         11       57.5         85
William  M         15       66.5        112

FULL SOLUTION
=============


* Ian Whitlock;

%macro utl_flatfile(lib=,  /* libref of input sas dataset to convert */
   sasdsn=,                /* second-level sas dataset name */
   file=print,             /* name of file to write to  */
   tl=3,                   /* default is to generate a title3 stmt  */
   spaces=1                /* number of spaces separating output fields */
   );
   %local putlist ;
   %let lib=%upcase(&lib);
   %let sasdsn=%upcase(&sasdsn);

   proc sql noprint ;
      select
        case
          when format ^= " " then
               name || " " || format
          when upcase(type) = "CHAR" then
               name || " $char"||trim(left(put(length,3.)))||"."
          else
               name || " best10."
        end  into :putlist separated by " +&spaces "
        from dictionary.columns
        where libname = "&lib" and memname = "&sasdsn"
      ;
      reset print ;
      title&tl "Layout for &lib..&sasdsn to &file" ;
      select name , type ,
        case
          when format ^= " " then trim(format)
          when upcase(type) = "CHAR" then "$"||trim(left(put(length,3.)))||"."
          else "best10."
        end  || " +&spaces " as fmtlen
        from dictionary.columns
        where libname = "&lib" and memname = "&sasdsn"
      ;
      title&tl ;
   quit;

   data _null_;
     set &lib..&sasdsn;
     file "&file" ;
     put &putlist ;
   run;
%mend utl_flatfile;


%utl_flatfile(lib=sashelp,sasdsn=class,file=d:/txt/class.txt);


* check;
data cls;
  infile "d:/txt/class.txt";

  input
     name           $8. +1
     sex            $1. +1
     age            best10. +1
     height         best10. +1
     weight         best10. +1
  ;
run;quit;

Re: Keep blanks when exporting to .txt

cat2 — Tue, 28 Feb 2017 08:22:25 GMT

Thank you! So easy! Solved my problem perfectly!