Hello,
I have been using the dataset Class from sashelp.class as data source to illustrate what I would like to do.
So, here's the case scenario. We have many datasets, from dataset1 to dataset4. In each dataset, we have 5 records except the last one (dataset4) which has 4 records.
The dataset class3 is generated randomly to provide a list of records to be deleted from the original dataset, and we also need to keep a trace of the information that was deleted somewhere else. (dataset1A -- dataset4A).
Also, I would like to open a dataset, delete all the requested records in one shot then closed it.
So, I wonder if the script below is deleting all the requested records per dataset in one shot.
data lookup;
input name $8. dsname $9.;
datalines;
Alfred dataset1
Alice dataset1
Barbara dataset1
Carol dataset1
Henry dataset1
James dataset2
Jane dataset2
Janet dataset2
Jeffrey dataset2
John dataset2
Joyce dataset3
Judy dataset3
Louise dataset3
Mary dataset3
Philip dataset3
Robert dataset4
Ronald dataset4
Thomas dataset4
William dataset4
;
run;
data class;
set sashelp.class;
run;
proc sql;
create table class2 as
select a.*,
b.dsname
from class as a
inner join lookup as b
on(a.name=b.name);
quit;
%macro multipleds;
%do j=1 %to 4;
%let name=%sysfunc(cats(dataset,&j));
%put &name.;
Data &name.;
set class2;
if compress(dsname) eq %tslit(dataset&j.) then output dataset&j.;
run;
%end;
%mend multipleds;
%multipleds;
data temp (keep=name);
if 0 then set SASHELP.CLASS nobs=NOBS;
do I=1 to 10;
N=rand('uniform',1,NOBS);
OBS=N;
set SASHELP.CLASS point=N;
output;
end ;
stop ;
run;
/**** class3 contains the list of records to be deleted ****/
proc sql;
create table class3 as
select a.*
from lookup as a
inner join temp as b
on(a.name=b.name);
quit;
/********* put deleted records into new datasets *************/
data _null_;
set class3;
dsname2=cats(dsname,"A");
call execute
(compbl(cat(
"proc sql;",
"create table ", dsname2, " as ",
"select a.* ",
"from ",dsname," as a ",
"where name in (select name from class3) ",
"having dsname eq ",%tslit(dsname),";quit;"
)));
run;
/********* Deleting few records per datasets *************/
data _null_;
set class3;
call execute
(compbl(cat(
"proc sql;",
"delete ",
"from ",dsname,
"where name in (select name from class3 having dsname eq ",%tslit(dsname),");quit;"
)));
run;
%macro multipleds2;
%do j=1 %to 4;
%let name=%sysfunc(cats(dataset,&j));
%put &name.;
Data &name.;
set &name.;
run;
%end ;
%mend multipleds2;
%multipleds2;
... View more
