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
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;
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
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
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)
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.
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;
unfortunately do not have the variables formatted.
however great job. thank you very much.
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!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: