BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
LinusH
Tourmaline | Level 20
Use call execute in a data step that is reading your Excel.
Data never sleeps
vraj1
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

vraj1
Quartz | Level 8

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.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

data_null__
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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