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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.