topic Re: Appending Daily dataset to monthly in SAS Programming

Appending Daily dataset to monthly

SAS_INFO — Mon, 06 Mar 2017 05:26:17 GMT

Hi Experts,

I need to creat monthly datasets appending daily datasets for the past 2 months.

Please suggest macros to do the same.

Regards,

aruna

Re: Appending Daily dataset to monthly

Shmuel — Mon, 06 Mar 2017 05:36:54 GMT

What name convention do you have or want to the daily/monthly datasets ?

Re: Appending Daily dataset to monthly

SAS_INFO — Mon, 06 Mar 2017 05:58:40 GMT

the name convention are as follows

ab002_20170303

ab002_20170304

ab002_20170305

and i want teh monthly dataset as AB002_17JAN,AB002_17FEB etc.,

AB002_17JAN should have all the daily datasets appended

Re: Appending Daily dataset to monthly

RW9 — Mon, 06 Mar 2017 09:47:28 GMT

Its always a good idea to post some test data in the form of a datastep. You don't need macro:

data ab002_20170303;
  a=1;
run;
data ab002_20170304;
  a=2;
run;
data ab002_20170305;
  a=3;
run;

data _null_;
  do i="201703";
    outfile=cats('ab002_17',put(month(input(cats(i,'01'),yymmdd10.)),month3.));
    call execute('data '||strip(outfile)||'; set ab002_'||strip(i)||':; run;');
  end;
run;

Re: Appending Daily dataset to monthly

Shmuel — Mon, 06 Mar 2017 10:31:32 GMT

Next macro program deletes previous monthly dataset and appends all monthly existing datasets:

%macro cre_monthly(prefix,yymm);
    proc datasets lib=work nolist; delete &prefix.&yymm; run;

    %do i=1 %to 31;
       %if %length(&i) = 1 %then %let i=0&i;
       %let dsn = &prefix.&yymm.&i;
       %if %sysfunc(exist(&dsn)) %then %do;
           proc append base=&prefix&yymm  data=&dsn;
           run;
       %end;
    %end;
%mend cre_monthly;
%cre_monthly(ab002_,201703);  /* usage example */

Re: Appending Daily dataset to monthly

Ksharp — Mon, 06 Mar 2017 10:51:03 GMT

data ab002_20170303 ab002_20170403;
  a=1;
run;
data ab002_20170304 ab002_20170404;
  a=2;
run;
data ab002_20170305 ab002_20170405;
  a=3;
run;

proc sql;
create table have as
 select memname,input(scan(memname,-1,'_'),yymmdd10.) as date format=yymmdd10.
  from dictionary.members 
   where libname='WORK' and calculated date is not missing
    order by 2;
quit;

data _null_;
 set have;
 by date groupformat;
 format date yymon7.;
 if first.date then call execute(cat('data ',scan(memname,1,'_'),'_',put(date,yymon7.),';set'));
 call execute(memname);
 if last.date then call execute(';run;');
run;

Re: Appending Daily dataset to monthly

SAS_INFO — Mon, 06 Mar 2017 22:46:24 GMT

hi Shmuel,

THank you for the reply and the code.

i dont want to dlete the previuos monthly data, but i want to develope dataset for each month.

EX: ab002_2017JAN, ab002_2017FEB, like wise for the past 24 months

Please suggest

Re: Appending Daily dataset to monthly

Shmuel — Tue, 07 Mar 2017 00:42:33 GMT

@SAS_INFO wrote: i dont want to dlete the previuos monthly data,

Be aware that if you append same daily dataset more than once - you will have duplicates in your monthly agregated dataset.

If you have tha daily_date as variable inside the daily dataset, then you can do:

data <monthly_dataset>;

set <monthly_dataset> (where=(date ne <daily_date>))

<daily>;

run;

instead the proc append;

Re: Appending Daily dataset to monthly

SAS_INFO — Tue, 07 Mar 2017 01:09:59 GMT

Hi Shmuel,

i get flat files, every day, and by excuting my code i generate sas datasets as below

ab002_20170207

ab003_20170207

ab013_20170207

I get 13 files every day , now , i have 2 things to do

1.I need to append it monthl wise , for the past 24 months

2. I need to write code that does this for the ongoing( for ongoing i guessthe following code works)

proc append base=ab002_2017JAN data=ab002_&tdate;

run;

&tdate is the macro that has everydays date.

Plz correct me if am wrong

Re: Appending Daily dataset to monthly

Shmuel — Tue, 07 Mar 2017 01:09:06 GMT

You are correct, just pay attention:

first time you run proc append with base=monthly_datset the file will be created.

next days will be appended to the existing monthly_dataset.

In case of some failure in the middle of appending, and you rerun same date again,

you mught have duplicates. See my previous post.

Re: Appending Daily dataset to monthly

SAS_INFO — Tue, 07 Mar 2017 06:28:08 GMT

hi Shmuel,

i tried the following code.

GOPTIONS ACCESSIBLE;

24 %macro cre_monthly(prefix,yyyymm);

25 proc datasets lib=eag_rep nolist; &prefix.&yymm; run;

27 %do i=1 %to 31;

28 %if %length(&i) = 1 %then %let i=0&i;

29 %let dsn = &prefix.&yyyymm.&i;

30 %if %sysfunc(exist(&dsn)) %then %do;

31 proc append base=eag_rep.et002_2017FEB data=&dsn;

32 run;

33 %end;

34 %end;

35 %mend cre_monthly;

36 %cre_monthly(et002_,201702); /* usage example */

WARNING: Apparent symbolic reference YYMM not resolved.

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "YYMM".

36 et002_&

______

180

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

NOTE: Statements not processed because of errors noted above.

but the output dataset appended only till 20170209 , not after that also i got the above error.

Re: Appending Daily dataset to monthly

Shmuel — Tue, 07 Mar 2017 10:14:03 GMT

You should choose argument (macro variable) name - is it yymm or yyyymm - you should be consistent.

Re: Appending Daily dataset to monthly

SAS_INFO — Fri, 10 Mar 2017 03:20:01 GMT

Hello All,

Finally i have developed a macro that helps me to append daily datasets to monthly datasets.

thanks to Shmuel...

%macro combine;

data eag_rep.AB003_2015dec;

set

%do i=1 %to 31;

%if %length(&i) = 1 %then %let i=0&i;

eag_rep.AB003_201512&i

%end;

;

run;

%mend;

%combine;

Re: Appending Daily dataset to monthly

RW9 — Fri, 10 Mar 2017 09:27:05 GMT

Sorry, that doesn't look like a solution at all. What happens in Febuary where there is either 28 or 29 days depending on leap year? Basically you will need to change this code for each month/year. Where as the code I provided, or using inbuilt lists is generic and does not require updating - don't be blinded by "needs" to use macro - it does absolutely nothing that Base SAS cannot do, other the obfuscate and complicate.

data eag_rep.AB003_2015dec;
  set eag_rep.AB003_201512:;
run;

Of course your whole system would be far more robust, easier to maintain and code with if you dropped putting "data" in table names/variable names, and put it in the data part of a dataset e.g:

data eag_rep.ab003;
  set eag_rep.ab003_2015: indsname=inds;  /* Get all data for year 2015 */
  filedate=input(tranwrd(inds,"ab003_",""),yymmdd10.);
run;

You would then have a large file with all your data in, be able to by group on filedate, be able to add new data merely by appending to that one dataset, and all still having the option to split the data up later on if you really need to. Simple.

Re: Appending Daily dataset to monthly

Shmuel — Fri, 10 Mar 2017 16:37:16 GMT

@SAS_INFO, @RW9 is right, you forget to check if the daily file realy exists:

%macro combine;
data eag_rep.AB003_2015dec;
set
%do i=1 %to 31;
    %if %length(&i) = 1 %then %let i=0&i;
    %if %sysfunc(exist(eag_rep.AB003_201512&i)) %then %do;
        eag_rep.AB003_201512&i
    %end;
%end;
;
run;
%mend;
%combine;

Re: Appending Daily dataset to monthly

SAS_INFO — Mon, 13 Mar 2017 00:11:54 GMT

Thanks for the code,

I want the files to be month wise, let me check and let u know