DATA Step, Macro, Functions and more

Passing a Comma Delimited Variable to a Macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Passing a Comma Delimited Variable to a Macro

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;


Accepted Solutions
Solution
‎11-08-2017 01:37 PM
Super User
Super User
Posts: 8,125

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to gsierra32

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;

View solution in original post


All Replies
Super User
Posts: 23,776

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to gsierra32

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?

Occasional Contributor
Posts: 6

Re: Passing a Comma Delimited Variable to a Macro

TXT files are in one folder.

Super User
Posts: 23,776

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to gsierra32

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...

Super User
Super User
Posts: 9,599

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to gsierra32

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;

 

Occasional Contributor
Posts: 6

Re: Passing a Comma Delimited Variable to a Macro

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.

Super User
Super User
Posts: 8,125

Re: Passing a Comma Delimited Variable to a Macro

[ Edited ]
Posted in reply to gsierra32

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;
Occasional Contributor
Posts: 6

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to 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.

Solution
‎11-08-2017 01:37 PM
Super User
Super User
Posts: 8,125

Re: Passing a Comma Delimited Variable to a Macro

Posted in reply to gsierra32

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;
Occasional Contributor
Posts: 6

Re: Passing a Comma Delimited Variable to a Macro

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.

Occasional Contributor
Posts: 6

Re: Passing a Comma Delimited Variable to a Macro

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 335 views
  • 4 likes
  • 4 in conversation