11-07-2017 10:50 PM
I want to write a macro to import different type of files. Let’s say we have files that are tab-delimited text files as well as CSV files. (“xyz.csv” and “abc.txt”). how do I achieve this?
11-07-2017 11:05 PM
First figure out all the variations you need and code a manual version. Determine what needs to change for each variation you need and add the corresponding macro variable.
Given PROC IMPORTs current set up I’m not sure you’re going to gain a whole lot by writing this macro though. Is there some problem you’re trying to solve with this macro?
11-08-2017 02:51 AM
Forget your macro, use proc import. You will never be able to reach the flexibility and stability of a SAS-created procedure.
And when you reach the stage of extracting data step code from proc import logs for repeated execution, stay with the data step code. Involving a macro layer will make your code less maintainable.
Now, OTOH, if you have a clearly defined basic layout of external files (like the fixed-block format typical on mainframes, or a csv with a given set of datatypes - eg coming from a DB system), you could start to write a macro that automatically creates the import data step off a list of field definitions. But at that stage the question of what delimiter to use would long be solved.
I do have such support macros. One handles the data transfer from the server (could be Linux or a mainframe), the other reads a single field and sets type, format, length, and detects all missing-value states. So the import procedure looks like
%make_filename(fileref,infile_name,params); data want; infile fileref; %read_field(name,type,length); %read_field(name,type,length); .... run;
This allows me to put an import job side-by-side with the PL-1 include that describes the external file and check if they fit, or where changes are.
("type" in this context is much more specific than just char or num. Think char, varchar, date, uuid, timestamp, binary, zoned, packed decimal, ....)