I have two text files, one containing raw data with no headers and another containing the associated column names and lengths. I'd like to use these two files to construct a single SAS dataset containing the data from one file with the column names and lengths from the other.
The file containing the data is a fixed-width text file. That is, each column of data is aligned to a particular column of the text file, padded with spaces to ensure alignment.
datafile.txt:
John 45 Has two kids
Marge 37 Likes books
Sally 29 Is an astronaut
Bill 60 Drinks coffee
The file containing the metadata is tab-delimited with two columns: one with the name of the column in the data file and one with the character length of that column. The names are listed in the order in which they appear in the data file.
metadata.txt:
Name 7
Age 5
Comments 15
My goal is to have a SAS dataset that looks like this:
Name | Age | Comments
-------+------+-----------------
John | 45 | Has two kids
Marge | 37 | Likes books
Sally | 29 | Is an astronaut
Bill | 60 | Drinks coffee
I want every column to be character with the length specified in the metadata file.
There has to be a better way than my naive approach, which is to construct a length statement and an input statement using the imported metadata, like so:
/* Import metadata */
data meta;
length colname $ 50 collen 8;
infile 'C:\metadata.txt' dsd dlm='09'x;
input colname $ collen;
run;
/* Construct LENGTH and INPUT statements */
data _null_;
length lenstmt inptstmt $ 1000;
retain lenstmt inptstmt '' colstart 1;
set meta end=eof;
call catx(' ', lenstmt, colname, '$', collen);
call catx(' ', inptstmt, cats('@', colstart), colname, '$ &');
colstart + collen;
if eof then do;
call symputx('lenstmt', lenstmt);
call symputx('inptstmt', inptstmt);
end;
run;
/* Import data file */
data datafile;
length &lenstmt;
infile 'C:\datafile.txt' dsd dlm='09'x;
input &inptstmt;
run;
This gets me what I need, but there has to be a cleaner way. One could run into trouble with this approach if insufficient space is allocated to the variables storing the length and input statements, or if the statement lengths exceed the maximum macro variable length.
Any ideas?
What you're doing is a fairly standard method of doing this. Yes, you could check things a bit more carefully; I would allocate $32767 for the two statements, for example, just to be cautious.
There are some ways you can improve this, though, that may take some of your worries away.
First off, a common solution is to build this at the row level (as you do) and then use proc sql to create the macro variable. This has a larger maximum length limitation than the data step method (the data step method maximum is $32767 if you don't use multiple variables, SQL's is double that at 64kib).
proc sql;
select catx(' ',colname,'$',collen)
into :lenstmt separated by ' '
from meta; *and similar for inputstmt;
quit;
Second, you can surpass the 64k limit by writing to a file instead of to a macro variable. Take your data step, and instead of accumulating and then using call symput, write each line out to a temp file (or two). Then %include those files instead of using the macro variable in the input datastep - yes, you can %include in the middle of a datastep.
There are other methods, but these two are the most common and should work for most use cases. Some other methods include call execute, run_macro, or using file open commands to work with the file directly. In general, those are either more complicated or less useful than the most common two, although certainly they are also acceptable solutions and not uncommon to see in practice.