Reading and compiling daily txt file with different dates

[carlos_jimenez]

I am have over 300 txt files to read; the only difference in the file name is the prefix date value. They range from 20190924 to 202000403.  I am having a hard time with a macro and loop to read and append all 300 files.  Below is are examples:

PCALB110_20190924.txt

PCALB110_20190925.txt

PCALB110_20190926.txt

....

PCALB110_20200403.txt

I was trying to do something like;

%macro STACK;

%DO I='23sep2019'd %TO '26sep2019'd;
data a;
infile "/share/gsas_vnx_frl/Users/Carlos_Jimenez/Automation/PCS _ADDRESS_CHANGE/Daily/PCALB110_%sysfunc(compress(put(&I.,yymmdd10.),"-")).txt" LRECL=335
ENCODING="LATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD ;
input
NAME_BEFORE_CHANGE $ 1-40
NAME_AFTER_CHANGE $ 41-80
CARD_NUMBER $ 81-96
ACCOUNT_TYPE $ 97-99
ACCOUNT_NUMBER $100-109
OLD_ADDRESS $ 110-149
OLD_CITY_STATE_ZIP $ 150-189
NEW_ADDRESS $ 190-229
NEW_CITY_STATE_ZIP $ 230-269
ADDRESS_CHANGE_DATE $ 270-277
OPERATOR_ID $ 278-285
OPERATOR_NAME $ 286-325
;
DATE="&I"; format date mmddyy10.;
run;

proc append data=a out=stack force;
run;

%END;
%MEND;

But, it didn't work.  Ideas??

[SAS_Cares]

Hi, @carlos_jimenez! Thanks for posting your query through our SAS Communities. For your specific issue, you may refer to this link  which discussed similar scenario like yours. Hope this helps.

[Tom]

Here are a couple of ideas why your code might not work.

1) The normal %EVAL() macro function that SAS uses in %IF and %DO statements does not recognize date literals. You can use %SYSEVALF() and it will recognize the date literals.

2) You are defining DATE as a string and then trying to attach the numeric format MMDDYY to it.

%DO I=%sysevalf('23sep2019'd) %TO %sysevalf('26sep2019'd);
...
date=&i;
...

3) When you use %SYSFUNC() to call regular SAS functions in macro code you need to wrap each function in separated %SYSFUNC() function call.  Also there is no need to remove quotes from the output of the PUT, it does not generate any quotes.  Put why not also not generate the hyphens either?  Then there is no need for the COMPRESS() function.  Also you cannot use PUT() with %SYSFUNC() since it cannot figure out if your format is numeric or character.  Use PUTN() since you are formatting a numeric value using a numeric format.

%sysfunc(putn(&I.,yymmddN10.))

4) I think you need to use BASE= not OUT= in PROC APPEND (unless it sees OUT= as an alias for BASE=).

Of course it would be easier to just read all of the files in a single data step. 

Or is it important that you only read the files whose names fall into that date range? 

[PGStats · Accepted solution]

To read all files at once and remember which file each row came from, use a wildcard:

length fromFile $400;
infile "/share/gsas_vnx_frl/Users/Carlos_Jimenez/Automation/PCS _ADDRESS_CHANGE/Daily/PCALB110_*.txt"
FILENAME=FN .... ;
fromFile = FN;

[Kurt_Bremser]

Once you have made your %do loop working (see the other suggestions), simplify your INFILE statement:

infile "/share/gsas_vnx_frl/Users/Carlos_Jimenez/Automation/PCS _ADDRESS_CHANGE/Daily/PCALB110_%sysfunc(putn(&I.,yymmddn8.)).txt" LRECL=335;