BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1800bigk
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

16 REPLIES 16
art297
Opal | Level 21

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__
Jade | Level 19

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
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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

1800bigk
Fluorite | Level 6

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
Opal | Level 21

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

1800bigk
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

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__
Jade | Level 19

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
Lapis Lazuli | Level 10

Hi data_null_;

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

Thank you very mych!

data_null__
Jade | Level 19

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

Linlin
Lapis Lazuli | Level 10

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)

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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