Subsetting & Merging observations

[Kiko]

Hello,

I have two txt files List A and List B. Both are a list of file numbers (6-digit all numeric). List A has 30 records and List B has 2000 records. Theoretically, everyone in List A should be in List B. I am trying to output a dataset (List C) that has everyone in List B that is  NOT in List A thus exclude the people in List A.

Next, with a newly created List C (n=1970), I need to merge it with another csv file called Info, which has the file number and variables I need. Again, theoretically, everyone in List C should be in Info as well. I’d like to create a SAS dataset called Master, which has everyone in List C with the file number and some of the variables from Info.

Does anyone know how I can do this? Thank you so much. 

[ballardw · Accepted solution]

Other than reading and writing SAS doesn't really do much with CSV files. So do you have all of this data as SAS data sets? If not that's the first step.

The first part is pretty easy:

proc sql;
   create table ListC as
   select filenum from (select distinct filenum from listB)
   except
   select finenum from listA
   ;
quit;

The bit I () with distinct selects eace filenum only once from your ListB data set. Otherwise you could have multiples of the same value. Since you say that ListB has many more records than listA this number could be significant.

The second part if you are happy with listC is also easy:

Proc sql;
   create table Master as
   select Info.*
   from Listc
        left join
        info
        on ListC.filenum=Info.filenum
   ;
run;

If you have your listC or Info in a library that you need to reference then you will need to modify this to use an alias (short word to reference a data set). The structure used above ListC.filenum means "use the value of filenum from dataset ListC. If you have a library and try to use Lib.ListC.filenum then the code parser gets confused.

So you would use

From Lib.Listc as c

and refer to a variable in that set using  c.filenum. Similar with your Info data set. The select clause would use the alias such as I.*.

The select Info.* means get all of the variables in Info and left join / on is only those that match the filenum values in listc.

You don't have to sort the data as the procedure will do that internally.