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?
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?
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.
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?
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;
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";
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.
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!
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.