Help using Base SAS procedures

efficient way to uodate dataset

Reply
Frequent Contributor
Posts: 89

efficient way to uodate dataset

suppose I have two data sets which are "Main" and "Support":

Main Dataset:

ID Variable_1
1..........0
2..........0
3..........0
4..........0
5..........0
6..........0
7..........0
8..........0
9..........0

Support Dataset:

ID Variable_1
2..........False
4..........True
5..........False
6..........True
9..........True

I want to update the Main data set in a way that the Variable_1 will be re-valued based on information from Support, For example, for ID 4, because the corresponding Variable_1 in the Support data set is "True", the Variable_1 in the Main data set will be re-valued to 1; similarly, for ID 2, because the corresponding Variable_1 in the Support data set is "False", the Variable_1 in the Main data set will be kept 0.

So the new updated Main should be:

ID Variable_1
1..........0
2..........0
3..........0
4..........1
5..........0
6..........1
7..........0
8..........0
9..........1

What could be an efficient Data step or Proc SQL to accomplish such task?

Thanks.
Super Contributor
Super Contributor
Posts: 365

Re: efficient way to uodate dataset

Hello Littlestone,

This is a possible solution:
[pre]
data m;
input ID Variable_1;
datalines;
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
run;
data s;
input ID Variable_1 $;
datalines;
2 False
4 True
5 False
6 True
9 True
run;
proc SQL;
create table r as
select a.ID,
case
when UPCASE(b.Variable_1) = "TRUE" then 1
when UPCASE(b.Variable_1) = "FALSE" or UPCASE(b.Variable_1) = "" then 0
else -1
end as Variable_1
from m as a left join s as b
on a.ID=b.ID
;quit;
[/pre]
Sincerely,
SPR
Frequent Contributor
Posts: 89

Re: efficient way to uodate dataset

Thank you very much. It is just what I need!
Frequent Contributor
Posts: 89

Re: efficient way to uodate dataset

Hello, SPR

Sorry to bother you again. I noticed that in your code:


.......proc SQL;
create table r as
select a.ID,
case........



You used "select a.ID", what if there are many variables in dataset a? Can I use "select a.* ?
Super Contributor
Super Contributor
Posts: 365

Re: efficient way to uodate dataset

Hello Littlestone,

In general you can use a.* but in your request it was necessary to update variable_1.
If you say a.* it means that variable_1 (without any changes) is also included, so it will be necessary to create some variable_2 according to requirements in your initial post.

Sincerely,
SPR
Frequent Contributor
Posts: 89

Re: efficient way to uodate dataset

thank you all for great help
Super User
Posts: 9,687

Re: efficient way to uodate dataset

[pre]




data m;
input ID Variable_1;
datalines;
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
;
run;
data s;
input ID Variable_1 $;
datalines;
2 False
4 True
5 False
6 True
9 True
;
run;
[/pre]


proc format;



 invalue        varr



        
'False'=0



        
'True'=1;



run;



 



proc sql;



 create table result as



  select coalesce(s.id,m.id) as id ,coalesce(input(s.variable_1,varr.)
,m.variable_1)
as var



   from s right join m
on s.id = m.id



   ;



quit;



 



 



 



 



 



Ksharp



Message was edited by: Ksharp
Ask a Question
Discussion stats
  • 6 replies
  • 130 views
  • 0 likes
  • 3 in conversation