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

Hi,

 

I want to compare the table1 column (‘Var’) values with another table2 variable name based on key. If match then update the values from table1 ‘new_values’ to table2 which match above column.

 

Table1:

Key1

Var

New_values

1234

Value1

1

4674

Value2

9

5732

Sim_ind

6

6342

Value3

3

 

Table2:

Key1

Value1

Value2

Value3

Sim_ind

1234

3

2

2

3

4674

6

5

4

4

5732

2

3

6

2

6342

1

 

1

5

 

Output should be

Key1

Value1

Value2

Value3

Sim_ind

1234

1

2

2

3

4674

6

9

4

4

5732

2

3

6

6

6342

1

 

3

5

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

if sort isn't too expensive 

data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;


data table2;
input Key1	Value1	Value2	Value3	Sim_ind;
datalines;
1234	3	2	2	3
4674	6	5	4	4
5732	2	3	6	2
6342	1	.	1	5
;

data want;
merge table2 table1;
array t(*) Value1--Sim_ind;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
drop var new_values;
run;

View solution in original post

5 REPLIES 5
Astounding
PROC Star

Here's an easy program to write.  However, it only works when TABLE2 contains unique values for KEY1.  Duplicates spoil the result.

 

proc sort data=table1;

by key1;

run;

proc sort data=table2;

by key1;

run;

proc transpose data=table1 out=changes (drop=_name_);

by key1;

id var;

var new_values;

run;

data want;

update table2 changes;

by key1;

run;

LinusH
Tourmaline | Level 20
So what have you tried so far?
Data never sleeps
novinosrin
Tourmaline | Level 20

This code assumes key1 has NO duplicates. I trust your sample is good representative of your real.

data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;


data table2;
input Key1	Value1	Value2	Value3	Sim_ind;
datalines;
1234	3	2	2	3
4674	6	5	4	4
5732	2	3	6	2
6342	1	.	1	5
;

data want;
if _n_=1 then do;
	if 0 then set table1;
   dcl hash h(dataset:'table1');
   h.definekey('key1');
   h.definedata('var','New_values');
   h.definedone();
   end;
 set table2;
 array t(*) Value1--Sim_ind;
if h.find()=0 then do;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
end;
drop var new_values;
run;

 

 

novinosrin
Tourmaline | Level 20

if sort isn't too expensive 

data table1;
input Key1 Var:$10. New_values;
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;


data table2;
input Key1	Value1	Value2	Value3	Sim_ind;
datalines;
1234	3	2	2	3
4674	6	5	4	4
5732	2	3	6	2
6342	1	.	1	5
;

data want;
merge table2 table1;
array t(*) Value1--Sim_ind;
do _n_=1 to dim(t);
if vname(t(_n_)) eq var then do;
t(_n_)=New_values;
return;
end;
end;
drop var new_values;
run;
Ksharp
Super User
data table1;
input Key1 Var:$10. New_values;
var=upcase(var);
datalines;
1234 Value1 1
4674 Value2 9
5732 Sim_ind 6
6342 Value3 3
;


data table2;
infile cards expandtabs truncover;
input Key1	Value1	Value2	Value3	Sim_ind;
datalines;
1234	3	2	2	3
4674	6	5	4	4
5732	2	3	6	2
6342	1	.	1	5
;


data want;
if _n_=1 then do;
	if 0 then set table1;
   dcl hash h(dataset:'table1');
   h.definekey('key1','var');
   h.definedata('New_values');
   h.definedone();
   end;
 set table2;
 array t{*} Value1--Sim_ind;
 do i=1 to dim(t);
   var=upcase(vname(t{i}));
   if h.find()=0 then t{i}=new_values;
 end;
 drop i var New_values;
run;
proc print noobs;run;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 990 views
  • 1 like
  • 5 in conversation