Dear SAS experts
I have a dataset x1 and dataset x2. I would like for specific variables of x1 to take the value of other variables in x2 under specific conditions.
This is my suggestion for programming. I hope this makes sense.
data one;
input record_id inddato time;
datalines;
1 123 4
1 125 7
2 345
2 145 12
4 125
;
run;
data two;
input record_id inddato xx;
datalines;
2 345 12
4 125 17
;
run;
proc sql;
create table test as
select distinct a.* from two
join b.one
on a.inddato=b.inddato and a.record_id=b.record_id and record_id in (2,4)
then b.time=a.xx
; quit;
data want;
input record_id inddato time;
datalines;
1 123 4
1 125 7
2 345 12
2 145 12
4 125 17
;
run;
However this does not work. Any suggestions?
Kind regards
Solvej
You can do a left join and use COALESCE() function, which will return non-missing value.
proc sql;
create table test as
select distinct a.record_id,a.inddato,coalesce(a.time,b.xx) as time
from one as a
left join two as b
on a.inddato=b.inddato and a.record_id=b.record_id
; quit;
Alternatively you can also use update statement in data step.
proc sort data=one;
by record_id inddato;
proc sort data=two;
by record_id inddato;
run;
data one;
update one two(rename=(xx=time));
by record_id inddato;
run;
data one;
input record_id inddato time;
datalines;
1 123 4
1 125 7
2 345 .
2 145 12
4 125
;
run;
data two;
input record_id inddato xx;
datalines;
2 345 12
4 125 17
;
run;
data want;
merge one two(rename=(xx=time));
by record_id;
run;
SAS Output
The SAS System |
record_id | inddato | time |
---|---|---|
1 | 123 | 4 |
1 | 125 | 7 |
2 | 345 | 12 |
2 | 145 | 12 |
4 | 125 | 17 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.