Desktop productivity for business analysts and programmers

Changing values in a large dataset but keeping others

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Changing values in a large dataset but keeping others

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!


Accepted Solutions
Solution
‎09-29-2017 11:40 AM
Esteemed Advisor
Posts: 5,626

Re: Changing values in a large dataset but keeping others

Posted in reply to mmagnuson

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;

 

PG

View solution in original post


All Replies
Frequent Contributor
Posts: 118

Re: Changing values in a large dataset but keeping others

Posted in reply to mmagnuson

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?

Solution
‎09-29-2017 11:40 AM
Esteemed Advisor
Posts: 5,626

Re: Changing values in a large dataset but keeping others

Posted in reply to mmagnuson

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;

 

PG
Contributor
Posts: 50

Re: Changing values in a large dataset but keeping others

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

Super User
Posts: 10,850

Re: Changing values in a large dataset but keeping others

Posted in reply to mmagnuson

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

Contributor
Posts: 50

Re: Changing values in a large dataset but keeping others

Posted in reply to 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?

Contributor
Posts: 50

Re: Changing values in a large dataset but keeping others

Posted in reply to 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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 279 views
  • 1 like
  • 4 in conversation