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 Experts,

 

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

 

Please suggest macros to do the same.

 

Regards,

aruna

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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 */

View solution in original post

15 REPLIES 15
Shmuel
Garnet | Level 18

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

SAS_INFO
Quartz | Level 8

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Shmuel
Garnet | Level 18

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 */
SAS_INFO
Quartz | Level 8

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

Shmuel
Garnet | Level 18

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

           

SAS_INFO
Quartz | Level 8

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

Shmuel
Garnet | Level 18

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.

SAS_INFO
Quartz | Level 8

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;

26

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.

Shmuel
Garnet | Level 18

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

Ksharp
Super User
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;

SAS_INFO
Quartz | Level 8

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Shmuel
Garnet | Level 18

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

 

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
  • 15 replies
  • 3524 views
  • 0 likes
  • 4 in conversation