topic Dynamic monthly folder creation and exporting files into the folder in SAS Programming

Dynamic monthly folder creation and exporting files into the folder

SAS0606 — Tue, 21 May 2019 03:39:40 GMT

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?

Re: Dynamic monthly folder creation and exporting files into the folder

VDD — Tue, 21 May 2019 03:47:29 GMT

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.

Dynamic monthly folder creation and exporting files into the folder

SAS0606 — Tue, 21 May 2019 05:14:44 GMT

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;

Re: Dynamic monthly folder creation and exporting files into the folder

andreas_lds — Tue, 21 May 2019 06:57:21 GMT

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

Re: Dynamic monthly folder creation and exporting files into the folder

Kurt_Bremser — Tue, 21 May 2019 07:31:08 GMT

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;

Re: Dynamic monthly folder creation and exporting files into the folder

Kurt_Bremser — Tue, 21 May 2019 07:36:07 GMT

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.