Passing a Comma Delimited Variable to a Macro

[gsierra32]

MY MACRO keeps crashing and stating that a non-number argument is found in the %SCAN.

I am trying to go through a list of names of text files that are comma delimited and PROC IMPORT.

I use this technique for lists delimited by spaces with no issues, I need the comma delimiter b/c (obviously) the text file names have spaces.

%NameList =
FFIEC CDR Call Schedule RCA,
FFIEC CDR Call Schedule RCA,
FFIEC CDR Call Schedule RCI;

%macro RdTxttoSAS;
  %let i = 1;
  %let qtr = 06302012;
  %let name = %scan(%QUOTE(&NameList),&i,%STR(","));
  %do %while (&qtr ne );
     PROC IMPORT OUT=out.&NAME_&qtr
        DATAFILE= "..\BULKDATA\&NAME. &qtr..txt"
        DBMS=TAB REPLACE;
        GETNAMES=YES;
        DATAROW=3;
        GUESSINGROWS = MAX;
 RUN;
    %let i   = %eval(&i+1);
    %let name = %scan(%QUOTE(&NameList),&i,%STR(","));
  %end;
%mend RdTxttoSAS;

[Reeza]

If these are all the same type of files you can read them in with a single data step instead. 

If you'd like to consider this approach, some things you need are the code to read a single file and an understanding of how the files are stored, ie all in one folder, in multiple folders?

[gsierra32]

TXT files are in one folder.

[Reeza]

Then you can use a wild card method to reference your files and read all at once. 

1. Read one file with PROC IMPORT and get the code from the log. ALT+SHIFT + Mouse to not select line numbers.

2. Make sure the code is correct, ie appropriate types/lengths for ALL files. Check against the record layout file, if you have one. 

3. Take the Informat/format/INPUT statements and paste them here:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

[RW9]

This:

%NameList =

Is not valid SAS syntax.

This:

  %let name = %scan(%QUOTE(&NameList),&i,%STR(","));

Will result in datasets named:

FFIEC CDR Call Schedule RCA 06302012

Which is not valid SAS dataset names.

I would also advise against putting data in dataset names - in this case quater.  It just makes all coding effort more difficult.  Also, proc import is not a great method to import data - it guesses what the data looks like.  Meaning that each time you run it then you might get a different result - not good for future processing.

Try something like:

%let namelist=FFIEC CDR Call Schedule RCA,FFIEC CDR Call Schedule RCA,FFIEC CDR Call Schedule RCI;

data _null_;
  length fname $100;
  do i=1 t countw("&namelist.",",");
    fname=scan("&namelist.",i,",");
    call execute(cats('proc import datafile="',fname','" out=want',put(_n_,best.),
                         ' dbms=tab replace; getnames="yes"; datarow=3; guessingrows=max; run;'));
  end;
run;

[gsierra32]

Thanks so much for your comment about Proc Import. There are hundreds of these TXT files with thousands of variables. I am taking a shot. The TXT filenames are not SAS compliant, but I am trying to avoid re-writing the names of hundreds of files.

I will give your code a shot. While I have used Macros quite a bit, I have never used a do loop with a data statement. Cheers.

[Tom]

Make sure to use the delimiter(s) you want in the %SCAN() macro call. Currently you are telling it use both comma and double quote as delimiters.

Also make sure to use a valid name for the SAS dataset that want to create.  If your source filename has spaces then you cannot use them as dataset names. Plus they could be much longer than the 32 character limit on member names. So perhaps just number them?

%let NameList 
=FFIEC CDR Call Schedule RCA
,FFIEC CDR Call Schedule RCA
,FFIEC CDR Call Schedule RCI
;

%macro RdTxttoSAS;
  %local i qtr filename dsname ;
  %let qtr = 06302012;
  %do i=1 %to %sysfunc(countw(%superq(namelist),%str(,)));
    %let filename = ..\BULKDATA\%scan(%superq(NameList),&i,%str(,))&qtr..txt;
    %let dsname = out.N_&i._&qtr ;
PROC IMPORT OUT=&dsname  replace 
     DATAFILE= "&filename"
     DBMS=TAB 
;
    GETNAMES=YES;
    DATAROW=3;
    GUESSINGROWS = MAX;
RUN;
  %end;
%mend RdTxttoSAS;

[gsierra32]

These are not SAS data sets. I am reading in TXT files and reading out SAS datasets. I do comply with SAS data set name on way out but am trying to avoid renaming the TXT files b/c there are hundreds of them, actually.

[Tom · Accepted solution]

---

@gsierra32 wrote:

These are not SAS data sets. I am reading in TXT files and reading out SAS datasets. I do comply with SAS data set name on way out but am trying to avoid renaming the TXT files b/c there are hundreds of them, actually.

---

In the code you posted you were using the same macro variable to generate both the dataset name and the source file name.

Another way to make this easier to deal with in SAS macro code is to use something other than comma as the delimiter. Pipe character is a good choice since it is an invalid character to use in a file name.  That way you do not need macro quoting.

%do i=1 %to %sysfunc(countw(&filelist,|));
  %let name=%scan(&filelist,&i,|);
  ....
%end;

[gsierra32]

Oh, yes. My apologies... the code I have didn't include my attempt to use a SAS-compliant data set name for the OUT SAS data set.

[gsierra32]

Brialliant! A non-macro language delimiter. That's my favorite solution!

[LeonidBatkhan]

There are at least 2 errors in this code:

1) %NameList = ... is not a valid syntax. It should be %let NameList=...;

2) PROC IMPORT OUT=out.&NAME_&qtr line does not have a . separator for macro variable &NAME, therefore macro compiler thinks it's macro variable &NAME_ (as _ is a valid character for a macro variable name) and obviously cannot find it.

Using %QUOTE() macro function is correct for masking commas when passing the comma-separated value into the %scan() macro function. For more details and in-depth discussion see my recent blog post Passing comma-delimited values into SAS macros and macro functions