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

Hi,

 

I have a macro that uploads txt files with the filename statement from a folder. Names of those txt files are related to the date when they were made. My macro checks the folder and when file from let's say 12.03.2017 doesn't exist it reads another file from day after 13.03.2017. 

 

My question is: how can I programme my macro to read again the file from a day before if file from current day doesn't exist ?

 

example: there is no txt file for 12.03.2017 so the macro reads again the file from 11.03.2017.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

If you want to keep your original macro structure, you can still eliminate a lot of the intermediary steps. e.g., try the following:

 

%macro combine(path,start,end);
  %let _start=%sysfunc(inputn(&start.,anydtdte9.));
  %let _end=%sysfunc(inputn(&end.,anydtdte9.));
  %do fdate=&_start. %to &_end.;
    %let filename=%sysfunc(catt(&path.,
       %sysfunc(substr(%sysfunc(putn(&fdate.,yymmddn8.)),4,5)),R.txt));
    %if %sysfunc(fileexist(&filename.)) %then %let hold_filename=&filename.;
    %else %let filename=&hold_filename;
    data abcd;
      infile "&filename." truncover;
      input abcd $5.;
    run;

    proc append base=work.want data=work.abcd;
    run;
  %end;
 %mend combine;

%combine(/folders/myfolders/,01mar2017,12mar2017)

The one disclaimer is that the start date MUST have a file that exists in the path being searched.

 

Art, CEO, AnalystFinder.com

 

View solution in original post

12 REPLIES 12
Tom
Super User Tom
Super User

Sounds like you want to generate a filename based on a date and if it doesn't exist then change the filename.

%macro xx;
%let today=%sysfunc(date());
%let filename=%sysfunc(putn(&today,ddmmyyp10));
%if not %sysfunc(fileexist(&filename)) %then
%let filename=%sysfunc(putn(&today-1,ddmmyyp10))
;
%put &=today &=filename ;
%mend xx;
%xx;

Result when run on '12MAR2017'd .

TODAY=20890 FILENAME=11.03.2017

 

P.S. You will have less confusion from your users and also allow your filenames to sort in date order if you express your dates using Y,M,D order instead of D,M,Y or M,D,Y order. 

Jedrzej
Obsidian | Level 7

thank you for your answer, but i wasn't enough precise (sorry).

 

ok, so i have to infile dates from 01jan2017 to 10jan2017 and the name of the files are like this: for 1st january it is 70101Rtxt.

 

so I count the days and create a variable with filenames like those in folders

========================================

%let start = '01jan2017'd;
%let end = '13jan2017'd;
data dates;

len = intck('day', &start, &end);
format period $8.;

do i = 0 to len;
period=put(intnx('day', &start, i, 'same'),yymmddn8.);
set = compress(substr(period,4,5) || 'R');
file= compress(substr(period,4,5) || 'R.txt');
output;
end;
run;

===============================

 

the last variable "file" is exactly the same as the format of my txt files in folders.

 

now i create a macro which infiles my files

 

=====================================

 

%macro set();


proc sql;
select count(*) into :rows from work.dates;
quit;

%do i = 1 %to &rows %by 1;

data null;
set dates;
if _n_=&i;

call symputx('files',file);
call symput('year', compress(substr(period,1,4)));;
call symput('date', period);

run;




filename files_txt "C:\Users\.....";

 

Data abcd;

infile files_txt;

input abcd $5.;

run;

 

 


proc append base=work.abcd data=work.abcd;
run;

%end;


%mend set

====================================

 

so, for example if file 70107R.txt doesn't exist I want to upload again file 70106R.txt which exists.

 

 

Tom
Super User Tom
Super User

Even easier since you already have a data step.

 

data dates;
  length period set file oldfile $32;
  do i = 0 to  intck('day', &start, &end);
    period=put(intnx('day', &start, i, 'same'),yymmddn8.);
    set = substr(period,4,5) || 'R';
    file= cats(file,'.txt');
    if not fileexist(catx('\','C:\.....',file) then file=oldfile ;
    output;
    oldfile= file ;
  end;
run;

Once you have the list of filename in a data set there is no need for a macro to read the files.  You can use the FILEVAR= option in the INFILE statement. But since you want to read the same file more than once you might need to a introduce a trick.

* Make an empty file ;
filename dummy temp;
data _null_;
  file dummy;
run;

data abcd ;
  set dates;
  do fname = catx('\','C:\....',file),pathname( 'dummy') ;
    infile dat filevar=fname end=eof truncover ;
    do while (not eof) ;
      input abcd $5. ;
      output;
    end;
  end;
run;

By alternating between the real filename and the dummy empty file it will allow the INFILE statement to reopen the already read file from the beginning.  Otherwise if you have two records in DATES next to each other that both pointed to the same file the FILENAME statement wouldn't reset and so the second one would read 0 observations since the file was already at the end of file. 

art297
Opal | Level 21

If you want to keep your original macro structure, you can still eliminate a lot of the intermediary steps. e.g., try the following:

 

%macro combine(path,start,end);
  %let _start=%sysfunc(inputn(&start.,anydtdte9.));
  %let _end=%sysfunc(inputn(&end.,anydtdte9.));
  %do fdate=&_start. %to &_end.;
    %let filename=%sysfunc(catt(&path.,
       %sysfunc(substr(%sysfunc(putn(&fdate.,yymmddn8.)),4,5)),R.txt));
    %if %sysfunc(fileexist(&filename.)) %then %let hold_filename=&filename.;
    %else %let filename=&hold_filename;
    data abcd;
      infile "&filename." truncover;
      input abcd $5.;
    run;

    proc append base=work.want data=work.abcd;
    run;
  %end;
 %mend combine;

%combine(/folders/myfolders/,01mar2017,12mar2017)

The one disclaimer is that the start date MUST have a file that exists in the path being searched.

 

Art, CEO, AnalystFinder.com

 

Tom
Super User Tom
Super User

Art -  Looks pretty good. No need for CAT... functions in macro code.

%macro combine(path,start,end);
  %local _start _end fdate filename new_filename;
  %let _start=%sysfunc(inputn(&start.,anydtdte9.));
  %let _end=%sysfunc(inputn(&end.,anydtdte9.));
  %do fdate=&_start. %to &_end.;
    %let new_filename=&path.%substr(%sysfunc(putn(&fdate.,yymmddn8.)),4,5)R.txt;
    %if %sysfunc(fileexist(&new_filename.)) %then %let filename=&new_filename.;
    data abcd;
      infile "&filename." truncover;
      input abcd $5.;
    run;

    proc append base=work.want data=work.abcd;
    run;
  %end;
%mend combine;
Jedrzej
Obsidian | Level 7

Thank You, it works 🙂

also one quick question...I want to add to my data abcd a new variable with information that the file is a duplicate from &hold_filename.

any ideas ?

art297
Opal | Level 21

Sure, just add a couple of lines to the macro. e.g.:

 

%macro combine(path,start,end);
  %local _start _end fdate filename new_filename;
  %let _start=%sysfunc(inputn(&start.,anydtdte9.));
  %let _end=%sysfunc(inputn(&end.,anydtdte9.));
  %do fdate=&_start. %to &_end.;
    %let new_filename=&path.%substr(%sysfunc(putn(&fdate.,yymmddn8.)),4,5)R.txt;
    %if %sysfunc(fileexist(&new_filename.)) %then %do;
      %let filename=&new_filename.;
      %let newfield=original;
    %end;
    %else %let newfield=duplicate;
    
    data abcd;
      infile "&filename." truncover;
      input abcd $5.;
      duplicate="&newfield.";
    run;

    proc append base=work.want data=work.abcd;
    run;
  %end;
%mend combine;

%combine(/folders/myfolders/,01mar2017,12mar2017)

Art, CEO, AnalystFinder.com

Jedrzej
Obsidian | Level 7

hi,

 

I have a new problem because my  path has changed and I have to infile my txt files from FTP like this 

 

filename rates ftp '&fileR.txt'

cd='&year'

user='anonymous'

pass=""

host='

prompt;

 

how should I modify my code ? I tried to simply subset the old path, but it doesnt work showing "physical file doesnt exist"

 

thank you in advance

 

Tom
Super User Tom
Super User

Try manually coding one FILENAME statement using the FTP method to make sure you know how the syntax you need.  

If you really need to give it username and password then you probably do NOT want to use the PROMPT keyword if you are reading multiple files.  That could be extremely annoying to have to type your username and password over and over again.

 

Remember to use double quotes instaed of single quotes if you want the macro variable referencess or macro function calls to resolve.  Also make sure to add a period in front of the file extension.  SAS will treat the period in "&fileR.txt" as indicating that you want to resolve the macro variable named fileR and not the macro variable named fileRtxt.  So to get a period before txt you need a second one.

 

filename rates ftp "&fileR..txt" 
  cd="&year"
  user='anonymous'
  pass="myname@myhostname"
  host='ftp.who.where.what'
;

Then in your INFILE statement you can hard code the fileref RATES that thie FILENAME statement generated.

infile RATES truncover;

  

Jedrzej
Obsidian | Level 7

ok but I want also to check if file exist and if it doesn't let it write to the source 'duplicate'

 

i need to change this part of code

 

%let new_filename=&path;
    %if %sysfunc(fileexist(&new_filename.)) %then %do;
      %let filename=&new_filename.;
      %let newfield=original;
    %end;
    %else %let newfield=duplicate;

 instead of path 'c:\.....'

 

i want to write my ftp

 

filename rates ftp "&fileR..txt" 
  cd="&year"
  user='anonymous'
  pass="myname@myhostname"
  host='ftp.who.where.what'
;

so how can i do it. I tried to changed it in different ways but i miss something 

Tom
Super User Tom
Super User

I am not sure I understand the question.

 

Are you saying you are having trouble converting the old path you used when accessing the file directly on the same machine to the proper path to use when using FTP?  If so the best way is to try it.  Don't write a complex program just keep trying to allocate the FILEREF and use it until you figure out if you can put the full path into name or if you need to only put the name part in the name and the rest of the path into the CD option.  Also make sure you are using proper slashes and case.  Your FTP server might be unix server and want / between directories and require that the name match the case.  In a Unix filesystem files names like 'A.txt' and 'a.txt' are two different files.

 

Note for the FILEEXIST function I think tests if a physical path exists.  You probably need to use the FEXIST function instead to test if fileref points to a file that exists.

Jedrzej
Obsidian | Level 7

ok, thank you very much, I will try this tommorrow

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3666 views
  • 5 likes
  • 3 in conversation