DATA Step, Macro, Functions and more

Appending data

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Appending data

[ Edited ]

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

 


Accepted Solutions
Solution
‎06-01-2017 11:34 PM
Super User
Posts: 17,766

Re: Appending data

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


All Replies
Super User
Posts: 17,766

Re: Appending data

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; 

 

 

Contributor
Posts: 60

Re: Appending data

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.
Super User
Posts: 6,928

Re: Appending data


arunasaran 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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,766

Re: Appending data


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

Super User
Super User
Posts: 6,498

Re: Appending data

[ Edited ]

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;

 

Contributor
Posts: 60

Re: Appending data

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
Super User
Super User
Posts: 6,498

Re: Appending data

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.
Super User
Posts: 6,928

Re: Appending data

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,766

Re: Appending data

%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;
Contributor
Posts: 60

Re: Appending data

Awesome Reeza, its working great Smiley Happy Smiley Happy Smiley Happy
Super User
Super User
Posts: 7,392

Re: Appending data

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.

Contributor
Posts: 60

Re: Appending data

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
Solution
‎06-01-2017 11:34 PM
Super User
Posts: 17,766

Re: Appending data

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

Super User
Super User
Posts: 7,392

Re: Appending data

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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