DATA Step, Macro, Functions and more

date formats

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

date formats

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;


Accepted Solutions
Solution
‎01-21-2016 06:30 AM
Super User
Super User
Posts: 7,997

Re: date formats

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

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: date formats

[ Edited ]

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.

Regular Contributor
Posts: 161

Re: date formats

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.

Solution
‎01-21-2016 06:30 AM
Super User
Super User
Posts: 7,997

Re: date formats

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

 

Trusted Advisor
Posts: 1,118

Re: date formats

[ Edited ]

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).

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 239 views
  • 0 likes
  • 3 in conversation