BookmarkSubscribeRSS Feed
Kiko
Fluorite | Level 6

Hello,

 

I have a SAS dataset and I want to change the value of certain variables in the dataset. I’ve identified records (9-10) that have incorrect values and have their last name, DOB, and the variable called Mass that needs to be corrected. There are two different types of change that need to happen. For instance,

 

  1. The record that belongs to someone whose last name-Mendez and DOB-7/9/14 I want to change the value of Mass from 2 to 1.

 

  1. The record that belongs to someone whose last name-Williams and DOB-2/2/77 I want to keep the variable Mass 2 as it is AND find another record with the same last name and DOB (other values such as first name, weight will be different) and change the value of Mass from 1 to 2. Similiarly, if I find more than one matching record I want SAS to generate the total # of these matching records. For instance, if there are 4 matching records whose last name is Williams and DOB 2/2/77, Mass should be 4. 

 

So I plan to do this for each of those 10 records with incorrect values using last name and DOB. Does anyone know how to do this?

 

Thank you!

3 REPLIES 3
Reeza
Super User

Use IF THEN statements and include lots of comments to explain why you're doing this.

 

Or create a data set with the correct information and then use an UPDATE statement to update the data set.

 

if last_name = 'Mendez' and dob = '09Jul2014'd then mass=2; -> are you sure this won't catch other records?

If it will catch other records you can consider hard coding it, if you know the row numbers but this is a bad idea in my opinion. If the data is out of expected order you'll change the wrong record. You can reference the row using _n_.

 

if _n_ = 4 then mass=2; 

@Kiko wrote:

Hello,

 

I have a SAS dataset and I want to change the value of certain variables in the dataset. I’ve identified records (9-10) that have incorrect values and have their last name, DOB, and the variable called Mass that needs to be corrected. There are two different types of change that need to happen. For instance,

 

  1. The record that belongs to someone whose last name-Mendez and DOB-7/9/14 I want to change the value of Mass from 2 to 1.

 

  1. The record that belongs to someone whose last name-Williams and DOB-2/2/77 I want to keep the variable Mass 2 as it is AND find another record with the same last name and DOB (other values such as first name, weight will be different) and change the value of Mass from 1 to 2. Similiarly, if I find more than one matching record I want SAS to generate the total # of these matching records. For instance, if there are 4 matching records whose last name is Williams and DOB 2/2/77, Mass should be 4. 

 

So I plan to do this for each of those 10 records with incorrect values using last name and DOB. Does anyone know how to do this?

 

Thank you!




s_lassen
Meteorite | Level 14

Sounds like what you basically want to do is to change the value of the MASS variable to the number of records having that last name and DOB?

Something like this may work:

proc sort data=have;
  by last_name DOB;
run;

data counts;
  do mass=1 by 1 until(last.DOB);
    set have(keep=last_name DOB);
    by last_name DOB;
    end;
run;

data want;
  merge have(drop=mass) counts;
  by last_name DOB;
run;

 

Tom
Super User Tom
Super User

I wouldn't change the original data, make a new dataset that has the new information.

PROC SQL can merge summary statistics onto detail rows for you.

proc sql ;
create table want as 
select *,count(*) as MASS
from have
group by last_name, DOB
;
quit;

 If for some reason your original dataset already has a variable named MASS then you will need to drop that on the way in by using the DROP= dataset option.

from have (drop=MASS)

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
  • 3 replies
  • 651 views
  • 0 likes
  • 4 in conversation