BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_INFO
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

14 REPLIES 14
Reeza
Super User

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
Quartz | Level 8
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.
Kurt_Bremser
Super User

@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;
Reeza
Super User

@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. 

Tom
Super User Tom
Super User

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;

 

SAS_INFO
Quartz | Level 8
Hello,

I checked the code with some dummy datasets.


26 %macro combine(month);
27 %local last_month date ;
28 %let last_month=%sysfunc(intnx(month,%sysfunc(today()),-1,b));
29
30 data DATASET.AB002_%sysfunc(putn(&last_month,yymmn6));
31 set
32 %do date=&last_month %to %sysfunc(intnx(month(&last_month,0,e));
33 dataset.AB002_%sysfunc(putn(&date,yymmddn8))
34 %end;
35 ;
36 run;
37 %mend combine;
38 %combine;
MPRINT(COMBINE): data DATASET.AB002_201705;
ERROR: Expected close parenthesis after macro function invocation not found.
ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.
ERROR: The %TO value of the %DO DATE loop is invalid.
ERROR: The macro COMBINE will stop executing.
MPRINT(COMBINE): set
39
40 GOPTIONS NOACCESSIBLE;
ERROR: File WORK.GOPTIONS.DATA does not exist.
ERROR: File WORK.NOACCESSIBLE.DATA does not exist.
41 %LET _CLIENTTASKLABEL=;
42 %LET _CLIENTPROCESSFLOWNAME=;
43 %LET _CLIENTPROJECTPATH=;
44 %LET _CLIENTPROJECTNAME=;
45 %LET _SASPROGRAMFILE=;
46
47 ;*';*";*/;quit;run;
____
180
2 The SAS System 16:12 Thursday, June 1, 2017


ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DATASET.AB002_201705 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set DATASET.AB002_201705 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Tom
Super User Tom
Super User
So did you try fixing the syntax mistake and seeing if the logic works then?
One is pretty obvious looking at the log you posted.
Kurt_Bremser
Super User
%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.

Reeza
Super User
%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;
SAS_INFO
Quartz | Level 8
Awesome Reeza, its working great 🙂 🙂 🙂
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS_INFO
Quartz | Level 8
I have to do the same task for 4 tables, say AB002, AB003 and AB004 and AB005, how could I pass the parametes in a macro rather than writing the code for 4 tables seperatley
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2836 views
  • 1 like
  • 5 in conversation