topic Re: PROC SQL SELECT in SAS Programming

PROC SQL SELECT

Solvej — Tue, 13 Nov 2018 14:36:59 GMT

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

Re: PROC SQL SELECT

SuryaKiran — Tue, 13 Nov 2018 15:03:48 GMT

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;

Re: PROC SQL SELECT

novinosrin — Tue, 13 Nov 2018 15:11:56 GMT

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