Help using Base SAS procedures

Proc Append then Delete in a loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Proc Append then Delete in a loop

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


Accepted Solutions
Solution
‎11-03-2011 05:26 PM
PROC Star
Posts: 7,356

Proc Append then Delete in a loop

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)

View solution in original post


All Replies
Solution
‎11-03-2011 05:26 PM
PROC Star
Posts: 7,356

Proc Append then Delete in a loop

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)

Respected Advisor
Posts: 3,777

Re: Proc Append then Delete in a loop

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_

PROC Star
Posts: 7,356

Re: Proc Append then Delete in a loop

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.

Occasional Contributor
Posts: 19

Re: Proc Append then Delete in a loop

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.

PROC Star
Posts: 7,356

Re: Proc Append then Delete in a loop

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

Occasional Contributor
Posts: 19

Re: Proc Append then Delete in a loop

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.

PROC Star
Posts: 7,356

Re: Proc Append then Delete in a loop

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

Occasional Contributor
Posts: 19

Re: Proc Append then Delete in a loop

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

Super User
Super User
Posts: 6,497

Re: Proc Append then Delete in a loop

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;

Regular Contributor
Posts: 184

Re: Proc Append then Delete in a loop

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?

Respected Advisor
Posts: 3,777

Re: Proc Append then Delete in a loop

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.

Super Contributor
Posts: 1,636

Re: Proc Append then Delete in a loop

Hi data_null_;

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

Thank you very mych!

Respected Advisor
Posts: 3,777

Re: Proc Append then Delete in a loop

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

Super Contributor
Posts: 1,636

Re: Proc Append then Delete in a loop

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)

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 2440 views
  • 0 likes
  • 7 in conversation