Proc Append then Delete in a loop

[1800bigk]

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

[art297 · Accepted solution]

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;

  %end;

%mend doit;

%doit (999,1001)

[data_null__]

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_

[art297]

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.

[1800bigk]

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

-

22

-----

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.

[art297]

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

[1800bigk]

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.

[art297]

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

[1800bigk]

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

[Tom]

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;

[Howles]

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?

[data_null__]

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.

[Linlin]

Hi data_null_;

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

Thank you very mych!

[data_null__]

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

[Linlin]

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)