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!
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.
Use datastep and put:
data _null_; file "afile.txt"; put var1 $10. var2 $20.; run;
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.
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;
Thank you! So easy! Solved my problem perfectly!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: