BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lutherh
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

To delete without creating a new dataset:

 

proc sql;
delete from VisitCost
where custId not in (select custId from HairColor);
quit;
PG
lutherh
Calcite | Level 5
Thank you!

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
  • 791 views
  • 1 like
  • 2 in conversation