topic Re: Making changes to records in SAS dataset in SAS Programming

Making changes to records in SAS dataset

Kiko — Tue, 19 Dec 2017 16:16:09 GMT

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,

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.

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!

Re: Making changes to records in SAS dataset

Reeza — Tue, 19 Dec 2017 16:22:09 GMT

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,

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.

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!

Re: Making changes to records in SAS dataset

s_lassen — Thu, 21 Dec 2017 21:14:56 GMT

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;

Re: Making changes to records in SAS dataset

Tom — Fri, 22 Dec 2017 06:21:05 GMT

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)