topic Re: Appending data in SAS Programming

Appending data

SAS_INFO — Thu, 01 Jun 2017 05:49:00 GMT

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

Re: Appending data

Reeza — Thu, 01 Jun 2017 05:53:35 GMT

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;

Re: Appending data

Tom — Thu, 01 Jun 2017 05:57:04 GMT

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;

Re: Appending data

SAS_INFO — Thu, 01 Jun 2017 05:57:54 GMT

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.

Re: Appending data

Kurt_Bremser — Thu, 01 Jun 2017 06:06:50 GMT

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

Re: Appending data

Reeza — Thu, 01 Jun 2017 06:11:03 GMT

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

Re: Appending data

SAS_INFO — Thu, 01 Jun 2017 06:13:10 GMT

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

Re: Appending data

Tom — Thu, 01 Jun 2017 06:19:17 GMT

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.

Re: Appending data

Kurt_Bremser — Thu, 01 Jun 2017 06:32:16 GMT

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

Re: Appending data

Reeza — Thu, 01 Jun 2017 06:35:24 GMT

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

Re: Appending data

SAS_INFO — Thu, 01 Jun 2017 07:12:37 GMT

Awesome Reeza, its working great 🙂 🙂 🙂

Re: Appending data

RW9 — Thu, 01 Jun 2017 08:09:35 GMT

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.

Re: Appending data

SAS_INFO — Fri, 02 Jun 2017 00:42:12 GMT

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

Re: Appending data

Reeza — Fri, 02 Jun 2017 02:17:15 GMT

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

Re: Appending data

RW9 — Fri, 02 Jun 2017 08:16:52 GMT

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.