I have 2 datasets one has subject with start_date and other with subject with few missing first drug date.
I want to get the start_date of first dataset if my second dataset first drug date is missing(only if missing), else use the same one.
can any one help me in this. i am using the below code but not wiorking
proc sql;
create table subj_dose2 as
select distinct trl_id
, subj_id
,src_start_dt
from ddm.subj_comp_dos
where topic_cd in ("BIASP")
and dose > 0
order by trl_id
, subj_id
,src_start_dt;
quit;
proc sort data=ddm.subj out=subj_tmp;
by trl_id subj_id;
run;
***Update exposed flag in ddm.subj***;
data subj(drop=src_start_dt);
merge subj_tmp(in=subj)
subj_dose2(in=dose2);
by trl_id subj_id;
if subj;
if fdrg_dt ne . then do;
rename src_start_dt=fdrg;
end;
run;
You will of coursehave to change any code to match your data as you didn't supply any. Depending on what you want the output variable type to be:
Numeric
coalesce(A.DRUG_DATE,input(B.START_DATE,date9.)) as DRUG_DATE
Char
coalesce(put(A.DRUG_DATE,date9.),B.START_DATE) as DRUG_DATE
Posting test data in the form of a datastep would help. At a guess something like:
proc sql; create table WANT as select A.TRT_ID, A.SUBJID, coalesce(A.DRUG_DATE,B.START_DATE) as DRUG_DATE from HAVE A left join START_DATES B on A.SUBJID=B.SUBJID; quit;
The coalesce() function takes the first non-missing from the list of variables. The same logic works in datastep.
Thanks,
But in this case the drug date is in date9.(numeric) and start date is in char.
i get the below error
ERROR: The COALESCE function requires its arguments to be of the same data type.
You will of coursehave to change any code to match your data as you didn't supply any. Depending on what you want the output variable type to be:
Numeric
coalesce(A.DRUG_DATE,input(B.START_DATE,date9.)) as DRUG_DATE
Char
coalesce(put(A.DRUG_DATE,date9.),B.START_DATE) as DRUG_DATE
The RENAME statement is a declarative statement. Hence, you can't execute it conditionally (with IF/THEN). Its purpose is to change variable names, not to transfer values from one variable to another.
To impute a missing value of variable X with the value of variable Y in the same observation (without overwriting non-missing values of X), you can use a conditional assignment statement like if x=. then x=y; (but there are alternative approaches as well, see @RW9's post).
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.