topic Re: Changing values in a large dataset but keeping others in SAS Enterprise Guide

Changing values in a large dataset but keeping others

mmagnuson — Fri, 29 Sep 2017 00:25:30 GMT

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!

Re: Changing values in a large dataset but keeping others

ShiroAmada — Fri, 29 Sep 2017 00:58:01 GMT

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?

Re: Changing values in a large dataset but keeping others

PGStats — Fri, 29 Sep 2017 03:21:57 GMT

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;

Re: Changing values in a large dataset but keeping others

mmagnuson — Fri, 29 Sep 2017 11:40:12 GMT

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

Re: Changing values in a large dataset but keeping others

Ksharp — Fri, 29 Sep 2017 14:40:43 GMT

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

Re: Changing values in a large dataset but keeping others

mmagnuson — Fri, 29 Sep 2017 15:11:26 GMT

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?

Re: Changing values in a large dataset but keeping others

mmagnuson — Fri, 29 Sep 2017 15:40:39 GMT

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!