BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmagnuson
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
ShiroAmada
Lapis Lazuli | Level 10

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
Opal | Level 21

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
mmagnuson
Quartz | Level 8

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

Ksharp
Super User

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

mmagnuson
Quartz | Level 8

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
Quartz | Level 8

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!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 882 views
  • 1 like
  • 4 in conversation