Changing values in a large dataset but keeping others

[mmagnuson]

Hello,

I have a dataset of names and information for a 5 month period.  The names in the dataset have one row of information per month. For example, John Smith has 5 rows of information: Jan, Feb, March, April, and May data.  One column is "Is_Valid".  Looking at all of the records there are different "Is_Valid" values depending on the month.  John Smith, for example, has "Is_Valid" values of "Yes" and "No" depending on the month.  I want to change John Smith's "Is_Valid" value to "Yes" for all 5 months but keep other individual records as is.

Is there a way to do this so that going forward if I want to change another individauls "Is_Valid" value, for example, Tom Smith to "Yes" for all 5 months I can do that as well?

My thought was to have an excel document where I can enter names of people to import into SAS so that it can be easily updated. I'm not sure how I would be able to change the information while keeping the other data the same. 

Hopefully this makes sense.

Thank you!

[ShiroAmada]

Try ths.....

proc sql;
  create table want as
  select id, month, is_valid, max(is_valid) as is_valid_now
from HAVE
group by id;
quit;

Note that i created a new variable is_valid_now for you to see what happened.  You can change this to

 select id, month, max(is_valid) as is_valid

Hope this helps, kabayan?

[PGStats · Accepted solution]

Assuming you have a dataset called YesNames of names for which you want to change Is_valid to Yes, you could use a SQL UPDATE query to change the values in your table without creating a new table.

proc sql;
update myTable
set is_valid = "Yes" where name in (select name from YesNames);
quit;

[mmagnuson]

Is there a way to update the table like that using the query builder? 

[Ksharp]

data class;
 set sashelp.class;
run;
data class;
 modify class;
 if age=14 then name='xx';
 run;

[mmagnuson]

So I imported an excel sheet that had the names of individuals and created a computed column of "Is_Valid" and had a case statement for t1 Name = t2Name show "YES" else "t2.IS_Valid"  and it worked.  So I renamed the computed column to "Is_Valid" and took out the original "Is_Valid" column from the new dataste.  I then edited my program so that it is "FROM New.Dataset isntead of "Dataset" but now my program errors because it says that there is no "Is_Valid" column....is it because the old column was a categorical variable and this one is a computed column? Is there a way to fix this easily? Or did I do something wrong?

[mmagnuson]

Nevermind. I had one letter capitalized that was supposed to be lowercased..... gotta love SAS not letting you get away with anything.

Thanks for your help everyone!