topic Re: select list of subjects from all datasets while copying in SAS Programming

select list of subjects from all datasets while copying

vraj1 — Fri, 21 Oct 2016 09:05:47 GMT

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

Re: select list of subjects from all datasets while copying

LinusH — Fri, 21 Oct 2016 09:13:05 GMT

Use call execute in a data step that is reading your Excel.

Re: select list of subjects from all datasets while copying

vraj1 — Fri, 21 Oct 2016 09:15:13 GMT

Can you please give an example how can i use it in my case.

Re: select list of subjects from all datasets while copying

RW9 — Fri, 21 Oct 2016 09:30:56 GMT

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.

Re: select list of subjects from all datasets while copying

vraj1 — Fri, 21 Oct 2016 10:35:02 GMT

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.

Re: select list of subjects from all datasets while copying

RW9 — Fri, 21 Oct 2016 11:17:49 GMT

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.

Re: select list of subjects from all datasets while copying

data_null__ — Fri, 21 Oct 2016 12:44:29 GMT

@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?

Re: select list of subjects from all datasets while copying

RW9 — Fri, 21 Oct 2016 12:52:22 GMT

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.

Re: select list of subjects from all datasets while copying

data_null__ — Fri, 21 Oct 2016 12:57:37 GMT

@RW9 You may not be copying but @vraj1 is that is the first step "he" shows.