BookmarkSubscribeRSS Feed
BlueNose
Quartz | Level 8

Hello

I have several datasets, each containing a "subject id" column. One of the datasets includes only 1 variable, "subject id", but it doesn't store all subjects, but only those in the study, for example:

ID
1
2
5
6
9

Note that subjects 3,4,7,8 are excluded.

Now I want to read the other datasets (from some library) into matching datasets in the work library, BUT, I want to read only the rows with a "subject id" that appear in the column of the subjects dataset (above).

How do I do that ?

Let's assume the dataset of subjects in the study is called "subjects" and I want to read a dataset from library 'a' called 'safety' (which has a subject id column with all subjects and other columns)

Thank you !

2 REPLIES 2
LinusH
Tourmaline | Level 20

SQL inner join.

Data never sleeps
Tom
Super User Tom
Super User

1) Get a list of the members in the library.

2) Use it to generate code to merge the subset dataset with each member and generate a work dataset.

Let's assume that the id variable is named ID and that the dataset with the the subject list is named SUBSET.

Also let's assume that each dataset is sorted (or has an index) by ID.

Here is example using PROC CONTENTS and a data _null_ step to generate a data step merge for each dataset.

%let libref=MYLIB;

%let idvar=ID ;

%let ds=SUBSET ;

proc contents data=&libref.._all_ noprint out=_contents ; run;

filename code temp ;

data _null_;

    file code ;

    set _contents ;

    where upcase(name)="%upcase(&idvar)" and memname ne "%upcase(&ds)" ;

    put 'data ' memname ';'

    / "  merge &ds(in=in1) &libref.." memname ';'

    / "  by &idvar ;"

    / '  if in1;'

    / 'run;'

  ;

run;

%inc code / source2 ;

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
  • 2 replies
  • 797 views
  • 0 likes
  • 3 in conversation