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-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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.

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
  • 16 replies
  • 5758 views
  • 0 likes
  • 7 in conversation