DATA Step, Macro, Functions and more

How do I concatenate datasets by one variable?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How do I concatenate datasets by one variable?

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!


Accepted Solutions
Solution
‎06-24-2016 02:48 PM
Respected Advisor
Posts: 4,654

Re: How do I concatenate datasets by one variable?

To delete without creating a new dataset:

 

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

View solution in original post


All Replies
Solution
‎06-24-2016 02:48 PM
Respected Advisor
Posts: 4,654

Re: How do I concatenate datasets by one variable?

To delete without creating a new dataset:

 

proc sql;
delete from VisitCost
where custId not in (select custId from HairColor);
quit;
PG
New Contributor
Posts: 3

Re: How do I concatenate datasets by one variable?

Thank you!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 205 views
  • 1 like
  • 2 in conversation