Do loop for date variable to import excel

[Pooja98]

Hi All,

I want to import excel file from each week (every friday) and append with the previous week. I need a macro for import and append (append is to convert weekly data into monthly data).

For example, if November 2021 contain 4 weeks of friday, i want to import 4 excel files and append to get the November monthly data.

Thanks in Advance,

Joe

Please help! 

[Tom · Accepted solution]

Where are the EXCEL files?

What are the NAME(s) of the EXCEL files?

What type of EXCEL files are they?

How to you tell which MONTH you want to read the files for?

If you want to find all of the FRIDAYs in a month use something like:

data friday;
  target=today();
  do n=1 to 5 while(date < NWKDOM(5, 5, month(target),year(target)));
    date=NWKDOM(n, 5, month(target),year(target));
    output;
  end;
  format target date date9.;
run;

Result

Obs       target    n         date

 1     18JAN2022    1    06JAN2022
 2     18JAN2022    2    13JAN2022
 3     18JAN2022    3    20JAN2022
 4     18JAN2022    4    27JAN2022

[Pooja98]

Excel files names are:

1. 20220107

2. 20220114

3. 20220121

4. 20220128

At the end of the month, i want to import the data for the mentioned above 4 weeks and want to append to get the entire week data, "Looking for a import code"