How do I concatenate datasets by one variable?

[lutherh]

Hi All,

I am using SAS 9.4 and have two SAS datafiles in which there are observations with the identifier, customer ID, listed as 'custID'.

The first file, "HairColor" has the following layout:

custID     sex     HairCol

   1            M       Black

   2            F        Brown

   3            F        Brown

   4            M        Red

  10           M        Black

The second file, "VisitCost" has the following layout:

 custID     VisDate    Cost     Sex

     2         12/01/10    150       F

     4         12/15/10    180       M

     4         12/28/10    210       F

     7         12/28/10    140       F

     9         12/31/10     500      M

    10        01/05/11     200      F 

I would like to be able to delete the observations in the "VisitCost" file that don't have a corresponding CustID in the "HairCol" file. Thus, the resulting dataset, "VisitCost2" would look like this:

custID     VisDate    Cost     Sex

     2         12/01/10    150       F

     4         12/15/10    180       M

     4         12/28/10     210      F

    10        01/05/11     200      F

Any help would be much appreciated!

[PGStats · Accepted solution]

To delete without creating a new dataset:

proc sql;
delete from VisitCost
where custId not in (select custId from HairColor);
quit;

[lutherh]

Thank you!