DATA Step, Macro, Functions and more

select list of subjects from all datasets while copying

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

select list of subjects from all datasets while copying

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


Accepted Solutions
Solution
‎10-21-2016 07:36 AM
Super User
Super User
Posts: 7,970

Re: select list of subjects from all datasets while copying

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.  

View solution in original post


All Replies
Super User
Posts: 5,431

Re: select list of subjects from all datasets while copying

Use call execute in a data step that is reading your Excel.
Data never sleeps
Regular Contributor
Posts: 161

Re: select list of subjects from all datasets while copying

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

Super User
Super User
Posts: 7,970

Re: select list of subjects from all datasets while copying

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.

Regular Contributor
Posts: 161

Re: select list of subjects from all datasets while copying

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.

 

 

Solution
‎10-21-2016 07:36 AM
Super User
Super User
Posts: 7,970

Re: select list of subjects from all datasets while copying

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.  

Respected Advisor
Posts: 3,799

Re: select list of subjects from all datasets while copying

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

Super User
Super User
Posts: 7,970

Re: select list of subjects from all datasets while copying

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,799

Re: select list of subjects from all datasets while copying

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 378 views
  • 0 likes
  • 4 in conversation