i am copying all datasets like below
proc datasets library=sdt;
copy out=sdttest;
quit;
In this i have a excel sheet with list of subjects and i only want those subjects copied in all the datasets from sdt.
Can anyone help me in this
For each datsset you have to process, as stored in sdt, this bit of code will be generated:
proc sql;
delete from SDT.<dataset>
where SUBJID not in (select distinct SUBJID from SUBJ);
quit;
Where dataset is replaced each time by the current dataset. The proc sql will remove from that dataset any subjectid records which are not in the list you imported from Excel - and stored in a dataset SUBJ.
So the original datasets get upated in place.
Can you please give an example how can i use it in my case.
Say you have all your data after this copy in sdt and in work a dataset of your subjects called subj:
data _null_; set sashelp.vtable (where=(libname="SDT")); call execute('proc sql; delete from SDT.'||strip(memname)||' where SUBJID not in (select distinct SUBJID from SUBJ); quit;'); run;
This will generate a delete step for each of the dasets in SDT.
Sorry for asking it again.
after i do this step
proc datasets library=sdt;
copy out=sdttest;
quit;
I will import my excel sheet which has subject id's and then the below?
data _null_;
set sashelp.vtable (where=(libname="sdt"));
call execute('proc sql;
delete from SDT.'||strip(memname)||'
where SUBJID not in (select distinct SUBJID from SUBJ);
quit;');
run;
Is it right?
where will my new data which has datasets with the subject id's needed be saved.
For each datsset you have to process, as stored in sdt, this bit of code will be generated:
proc sql;
delete from SDT.<dataset>
where SUBJID not in (select distinct SUBJID from SUBJ);
quit;
Where dataset is replaced each time by the current dataset. The proc sql will remove from that dataset any subjectid records which are not in the list you imported from Excel - and stored in a dataset SUBJ.
So the original datasets get upated in place.
@RW9 Wouldn't it be more efficient to copy the data sets and select the subjects of interest in one SQL or DATA step than to copy all subjects and delete in a second step?
I am not copying anything? It simply generates a delete from step for each of the datasets. So he has a series of files in one libname, and for each of those, the ones in the subset from Excel get removed. No copying.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.