BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carlos_jimenez
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

4 REPLIES 4
SAS_Cares
SAS Employee

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
Super User Tom
Super User

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
Opal | Level 21

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;
PG
Kurt_Bremser
Super User

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;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 681 views
  • 1 like
  • 5 in conversation