topic Re: efficient way to uodate dataset in SAS Procedures

efficient way to uodate dataset

littlestone — Tue, 22 Mar 2011 20:18:00 GMT

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.

Re: efficient way to uodate dataset

SPR — Tue, 22 Mar 2011 20:58:55 GMT

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

Re: efficient way to uodate dataset

littlestone — Wed, 23 Mar 2011 01:54:00 GMT

Thank you very much. It is just what I need!

Re: efficient way to uodate dataset

littlestone — Wed, 23 Mar 2011 03:18:38 GMT

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.* ?

Re: efficient way to uodate dataset

Ksharp — Wed, 23 Mar 2011 04:53:07 GMT

[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

Re: efficient way to uodate dataset

SPR — Wed, 23 Mar 2011 13:52:29 GMT

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

Re: efficient way to uodate dataset

littlestone — Wed, 23 Mar 2011 17:16:06 GMT

thank you all for great help