Hi All,
I am currently using the below mentioned code to append daily datasets to monthly.
****Mothly Dataset************
data _null_;
myyear = year(today());
last_month = month(today())-1;
length string $6;
string = put(myyear,z4.) !! put(last_month,z2.);
call symput('last_month',string);
run;
%macro combine;
data DATASET.AB002_&last_month
set
%do i=1 %to 30;
%if %length(&i) = 1 %then %let i=0&i;
dataset.AB002_201611&i
%end;
;run;
%mend;
%combine;
TASK :
In this code i am supplying the end date of a month manually (30,31 28 or 29).
My requirement is i need to automate the whole code, and the last date of the month should get calculated dynamically, while appending the datasets to monthly.
My input files names are like AB002_20170501,
AB002_20170502
AB002_20170503....AB002_20170531
Add a parameter for the prefix.
Try and let us know if you have issues.
See a basic short macro tutorial here.
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/
I suggest reading all of it, but the primary focus would be under the heading.
A macro program for repeating a procedure multiple times
Look at the INTNX function to align your dates. The examples show the use of the 0 interval.
Why are you appending all each time? Why not create a master table and append to that each day - so only new records are added?
And you shoukd use PROC APPEND since it doesn't recreate the dataset it adds on and doesn't process every line of data as you would in a dataset.
And look at wildcards for datasets. The following are likely equivalent, but you should test it.
SET AB002_201705: ;
SET AB002_20170501 - AB002_20170531;
@SAS_INFO wrote:
Hi Reeza,
My input files are originally txt files , from which I build datasets daily,somehow, the daily files doesn't have todays data, and sometimes a day's data will appear after 2 days.
So I couldnot create monthly dataset and let the daily files to append ther directly.
my plan is to find a way to calculate the end date of a month(30,31, 28 or 29) and automate and schedule the whole process on 5th of every month, by that I am giving enough time to all the daily data to appear and then after a day or 2 I need to delete the daily datasets and preserve only monthly datasets.
So then do it in one data step without macro logic, only using macro variables:
%let libname=dataset;
%let basename=ab002_;
data _null_;
length ds_names $1300 ds_name $43;
lastmonth_start = intnx('month',today(),-1,'begin');
lastmonth_end = intnx('month',lastmonth_start,0,'end');
do curdate = lastmonth_start to lastmonth_end;
ds_name = "&libname..&basename" !! put(curdate,yymmddn8.);
ds_names = catx(' ',trim(ds_names),ds_name);
end;
call symput('ds_names',trim(ds_names));
run;
%put &ds_names; /* for control */
data want;
set &ds_names;
run;
@SAS_INFO wrote:
Hi Reeza,
My input files are originally txt files , from which I build datasets daily,somehow, the daily files doesn't have todays data, and sometimes a day's data will appear after 2 days.
So I couldnot create monthly dataset and let the daily files to append ther directly.
my plan is to find a way to calculate the end date of a month(30,31, 28 or 29) and automate and schedule the whole process on 5th of every month, by that I am giving enough time to all the daily data to appear and then after a day or 2 I need to delete the daily datasets and preserve only monthly datasets.
There are several things wrong with this logic but it's past my bedtime.
I wouls strongly suggest you pseudocode your process. Look at what is done daily and monthly and what needs to happen where. There's a coding philosophy called DRY - don't repeat yourself. I suspect you have a lot of repetition in your current process. This is pretty basic data management concepts so it may be worth reading up on some that if that's your current role. I know IT/Analyst roles can be fluid.
I'm pretty sure I've also linked you to my post about how to read all text files at once using wildcards and avoid these loops. It would still be applicable here. Note with the wildcard method you don't need to know how many days are in the month. It'll read all files with the correct year month prefix.
You should just let SAS generate the dates in the form you need.
%macro combine(month);
%local last_month date ;
%let last_month=%sysfunc(intnx(month,%sysfunc(today()),-1,b));
data DATASET.AB002_%sysfunc(putn(&last_month,yymmn6));
set
%do date=&last_month %to %sysfunc(intnx(month(&last_month,0,e));
dataset.AB002_%sysfunc(putn(&date,yymmddn8))
%end;
;
run;
%mend combine;
%combine;
%sysfunc(intnx(month(&last_month,0,e))
three opening, but only two closing parentheses.
Hint: one of the opening parentheses needs to be replaced by a comma.
%macro append_simple_code();
%let YMonth = %sysfunc(INTNX (month, %sysfunc(today()), -1, end), yymmn6);
Data AB002_&Ymonth;
Set AB002_&Ymonth: ;
Run;
%mend;
%append_simple_code;
Erm, maybe I am missing something here, but if you have a set of files with prefix AB0002 and you want to combine them all, just do:
data master; set master ab002:; run;
In the above, you would keep a dataset called master, this one is the master dataset, and then copy in your new files called ab002_.... and the above will set anything with the prefix onto master.
Add a parameter for the prefix.
Try and let us know if you have issues.
See a basic short macro tutorial here.
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/
I suggest reading all of it, but the primary focus would be under the heading.
A macro program for repeating a procedure multiple times
If you present your problem in full it saves this going back and forth. You can simply:
data _null_; do i=1 to 4; call execute(cats('data master',put(i,1.),'; set master',put(i,1.),' ab00',put(i,1.),':; run;')); end; run;
This will create master1 to 4 concatenating all ab00i datasets - where i is 1-4 - into the master.
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.