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
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)
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)
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_
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.
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.
You appear to have pasted a stray character (1) in your code. I copied, pasted and ran the code with no problem.
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.
Yes, that would definitely be why. Thus, instead, try the macro method I proposed earlier in this thread.
Thanks, that worked perfect. Ran quickly with ~ 200 hundred data sets so thanks again.
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;
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?
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.
Hi data_null_;
Why you use "if 0 then modify &base" instead of "modify &base"?
Thank you very mych!
Because this is just APPEND. We don't want to ready any data from BASE.
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)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: