Dynamic monthly folder creation and exporting files into the folder

[SAS0606]

i am trying to create folder dynamically for months like below and include the data exported file in the respective folder biweekly,

"D:\Documents\May\test1.xlsx

 D:\Documents\May\tes2.xlsx"

I have written the below code for creating the monthly folder in the respective directory,

data _null_;

rootdir='D:\Documents\';

today=today();

lastmonth=intnx('month',today,-1);

dir1=catx(' ',put(today,monname3.),year(today));

/*dir2= catx(' ',put(day(today),z2.),'-',put(today,monname3.));*/

newdir1=dcreate(dir1,rootdir);

/*newdir2=dcreate(dir2,newdir1);*/

put (rootdir dir1 newdir1) (=/);

run;

But I am stuck after this to export my data(excel file) in these folders dynamically. Can I get any help on this please on how should I go about it, to export my files for every month instead of manually updating the folder everytime? 

[VDD]

ask the person who's code you are trying to replace at your worksite. 

I am sure that they can assist you and know how to accomplice what you are seeking, since what your are asking is already in your code and has already been done for most of you.

[SAS0606 · Accepted solution]

i am trying to create folder dynamically for months like below and include the data exported file in the respective folder biweekly,

"D:\Documents\May\test1.xlsx

 D:\Documents\May\tes2.xlsx"

I have written the below code for creating the monthly folder in the respective directory,

data _null_;

rootdir='D:\Documents\';

today=today();

lastmonth=intnx('month',today,-1);

dir1=catx(' ',put(today,monname3.),year(today));

/*dir2= catx(' ',put(day(today),z2.),'-',put(today,monname3.));*/

newdir1=dcreate(dir1,rootdir);

/*newdir2=dcreate(dir2,newdir1);*/

put (rootdir dir1 newdir1) (=/);

run;

But I am stuck after this to export my data(excel file) in these folders dynamically. Can I get any help on this please on how should I go about it, to export my files for every month instead of manually updating the folder everytime?

[Kurt_Bremser]

First of all, I would VERY STRONGLY recommend (in the interest of your future mental sanity) that you not use month names with the year appended as directory names, but proper period designators that sort well and are not dependent on local conventions.

data _null_;
rootdir = 'D:\Documents\';
today = today();
dir1 = put(today,yymmd7.);
newdir1 = dcreate(dir1,rootdir);
put (rootdir dir1 newdir1) (=/);
call symputx('export_path',catx('\',rootdir,dir1));
run;

After that, you can use the newly created macro variable in proc export:

proc export
  data=mylib.mydata
  datafile="&export_path.\mydata.xlsx"
  dbms=xlsx
  replace
;
run;

[andreas_lds]

Add

call symputx('exportDir', newdir1);

to the data step you already have. Then, in the exporting steps, replace the path with with the macro-variable exportDir. Could look like:

ods excel file="&exportDir.\file.xlsx";

[Kurt_Bremser]

PLEASE DO NOT DOUBLE-POST! I just found out that I wasted time on answering a question that had already been sufficiently dealt with by others. And I had to merge the posts.