topic Re: Proc Append then Delete in a loop in SAS Procedures

Proc Append then Delete in a loop

1800bigk — Thu, 03 Nov 2011 20:38:08 GMT

I have files named D0912, D0913, D0914,,,D9999

I want to be able to do the code below for a given start file and end file.

proc append force

base = D0912

data = D0913;

run;

proc delete data = D0913;

run;

proc append force

base = D0912

data = D0914;

run;

proc delete data = D0914;

run;

proc append force

base = D0912

data = D0915;

run;

proc delete data = D0915;

run;

So on and so forth...

I want to just say start at D0912 and do that over and over until D9999.

Thanks

Proc Append then Delete in a loop

art297 — Thu, 03 Nov 2011 21:26:50 GMT

The following does what you asked for, but I post my response with A BIG WARNING.

First, make a copy of your starting dataset. What you said you wanted to do overwrites the first dataset. That is rather risky and I definitely wouldn't do it without backing up the data set.

Similarly, since you are deleting all of the other datasets as well, I would back them up before attempting to run the code.

That said:

/*create some test data*/

data d0999;

retain x (1);

run;

data d1000;

retain x (2);

run;

data d1001;

retain x (3);

run;

/*end of test data creation*/

%macro doit(first,last);

%do i=&first %to &last;

data _null_;

%if "&i" eq "&first" %then %do;

call symput('base',put(&first,z4.));

%end;

call symput('data',put(&i,z4.));

run;

%if "&i" ne "&first" %then %do;

proc append force

base = D&base.

data = D&data.;

run;

proc delete data = D&data;

run;

%end;

%mend doit;

%doit (999,1001)

Re: Proc Append then Delete in a loop

data_null__ — Thu, 03 Nov 2011 22:15:29 GMT

You don't need any macros or loops. Well the data step loop.

data D0912 D0913 D0914 D0915;
   set sashelp.class;
   run;
%let base=D0912;
%let append=D0913-D0915;
 
data &base;
   if 0 then modify &base;
   set &append open=defer;
   output;
   run;
proc datasets;
   delete &append;
   run;

I forgot about OPEN=DEFER which should improve performance.

Message was edited by: data _null_

Re: Proc Append then Delete in a loop

art297 — Thu, 03 Nov 2011 22:31:00 GMT

DN,

Nice! Did I say something, earlier today, about how difficult it is to see the best simplification? I, for one, would definitely mark your response as being the correct one.

Re: Proc Append then Delete in a loop

1800bigk — Fri, 04 Nov 2011 12:33:34 GMT

I am getting an error:

374 %let base=D0912;

375 %let append=D0913-D0915;

376 data &base;

377 if 0 then modify &base;

378 set &append open=defer;

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

1 D0913-D0915

-----

202

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, ;, END,

KEY, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Re: Proc Append then Delete in a loop

art297 — Fri, 04 Nov 2011 12:40:17 GMT

You appear to have pasted a stray character (1) in your code. I copied, pasted and ran the code with no problem.

Re: Proc Append then Delete in a loop

1800bigk — Fri, 04 Nov 2011 12:50:48 GMT

The dash between D0913 and D0915 is giving me the problem. The 22 in the error message should be under the dash not the 1.

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

1 D0913-D0915

..............-

.............22

Maybe it's because I am on version 9.1.

Re: Proc Append then Delete in a loop

art297 — Fri, 04 Nov 2011 13:03:36 GMT

Yes, that would definitely be why. Thus, instead, try the macro method I proposed earlier in this thread.

Re: Proc Append then Delete in a loop

Howles — Fri, 04 Nov 2011 13:04:01 GMT

Neat.

But how confident are we that this will scale to 9000+ data sets?

How about a hash-based solution, so that all of the housekeeping occurs at run time?

Re: Proc Append then Delete in a loop

Tom — Fri, 04 Nov 2011 13:15:42 GMT

In that case build a space delimited list. You need at least SAS 9.2 to allow ranges of dataset names.

proc sql noprint ;

select memname into :append separated by ' '

from dictionary.members

where libname='WORK'

and memname like 'D%'

order by memname

;

quit;

Re: Proc Append then Delete in a loop

1800bigk — Fri, 04 Nov 2011 13:16:14 GMT

Thanks, that worked perfect. Ran quickly with ~ 200 hundred data sets so thanks again.

Re: Proc Append then Delete in a loop

data_null__ — Fri, 04 Nov 2011 13:20:57 GMT

Scale? I'll leave that to others. I do expect it would scale better than looping PROC APPENDS.

OPEN=DEFER if it can be used should help.

I don't understand what a hash would add. Please enlighten me.

Re: Proc Append then Delete in a loop

Linlin — Fri, 04 Nov 2011 14:45:31 GMT

Hi art297,

Thanks for your Macro.

/*create some test data*/
data d0999;
retain x (1);
run;
data d1000;
retain x (2);
run;

data d1001;
retain x (3);
run;

data d1002;
retain x (4);
run;

/************************/
%macro doit(second,last);
%do i=&second %to &last;
    data _null_;
        call symput('data',put(&i,z4.));
    run;
         proc append force
              base = d0999
              data = D&data.;
         run;
         proc delete data = D&data;
         run;
%end;
%mend doit;
%doit (1000,1002)

Re: Proc Append then Delete in a loop

Linlin — Fri, 04 Nov 2011 15:49:29 GMT

Hi data_null_;

Why you use "if 0 then modify &base" instead of "modify &base"?

Thank you very mych!

Re: Proc Append then Delete in a loop

data_null__ — Fri, 04 Nov 2011 16:01:55 GMT

Because this is just APPEND. We don't want to ready any data from BASE.

Re: Proc Append then Delete in a loop

data_null__ — Fri, 04 Nov 2011 16:15:12 GMT

Since PROC APPEND is a statement in PROC DATASET I think you should at least nest the repeated APPENDs in PROC DATASETS. If SAVE statement is not be appropiate you can just gen a DELETE for the list of APPEND data sets.

data D0912 D0913 D0914 D0915;
   set sashelp.class;
   run;
 
%let base=D0912;
 
proc sql noprint;
   select catx(' ','APPEND BASE=',"&base",'DATA=',memname,';') into :append
      from dictionary.members
      where libname eq 'WORK' and upcase(memname) ne %upcase("&base");
   quit;
   run;
proc datasets;
   &append;
   run;
   save &base;
   run;
   quit;

Re: Proc Append then Delete in a loop

Ksharp — Mon, 07 Nov 2011 06:50:36 GMT

I think Art's code is good enough except for typing some more words. It is very fast way for large tabls.

Keep in mind that SAVE statement will only keep the &base , other tables will be deleted include other needed tables, so you should be careful to use it.

Ksharp