topic Re: insert missing values when condition not satisfied in SAS Programming

insert missing values when condition not satisfied

mano — Tue, 26 Feb 2013 04:28:18 GMT

Hi, I am creating a patient listing with all the events during the trial period. (*ARI=Acute Respiratory Illness)

EVENT_TYPE EVENT_DATE VISIT_DATE REASON_FOR_VISIT

ARI 01JUN2012 01JUN2012 ARI

ARI 20JUN2012 20JUN2012 ARI

ARI 04AUG2012 04AUG2012 ARI

ARI 04AUG2012 10AUG2012 ARI-WORSE

ARI 12SEP2012 12SEP2012 ARI

WORSE 01OCT2012 01OCT2012 WORSE

WORSE 01OCT2012 12OCT2012 WORSE

If an event is not cured and has an extra visit with different reason other than the reason mentioned in first visit , then event type should be missing.

In the above example, an event started on 04Aug2012 and it is not cured, so subject has visited later again on 10aug2012 (but event date will be the same as earlier date) mentioning the reason of visit as ARI-WORSE . So the output should have event type missing.

Another event started on 01oct2012 with WORSE and not cured, but in the next visit also subject mentioned reason as WORSE .In this case event type is not missing but same as the initial event type.

My final output should be as follows:-

EVENT_TYPE EVENT_DATE VISIT_DATE REASON_FOR_VISIT

ARI 01JUN2012 01JUN2012 ARI

ARI 20JUN2012 20JUN2012 ARI

04AUG2012 04AUG2012 ARI

04AUG2012 10AUG2012 ARI-WORSE

ARI 12SEP2012 12SEP2012 ARI

WORSE 01OCT2012 01OCT2012 WORSE

01OCT2012 12OCT2012 WORSE

Can anyone help me the code.

Thanks in advance.

Re: insert missing values when condition not satisfied

UrvishShah — Tue, 26 Feb 2013 06:30:55 GMT

Hi Mano,

Try the following SAS Code...

data want;

set have;

if event_date NE visit_date then event_type EQ " ";

else event_type EQ event_type;

run;

Thanks,

Urvish

Re: insert missing values when condition not satisfied

damanaulakh88 — Tue, 26 Feb 2013 07:10:30 GMT

Hi Mano,

Please find below code for your problem:-

data new;

informat EVENT_DATE date9. VISIT_DATE date9.;

input EVENT_TYPE $ EVENT_DATE VISIT_DATE REASON_FOR_VISIT $10.;

FORMAT EVENT_DATE date9. VISIT_DATE date9.;

datalines;

ARI 01JUN2012 01JUN2012 ARI

ARI 20JUN2012 20JUN2012 ARI

ARI 04AUG2012 04AUG2012 ARI

ARI 04AUG2012 10AUG2012 ARI-WORSE

ARI 12SEP2012 12SEP2012 ARI

WORSE 01OCT2012 01OCT2012 WORSE

WORSE 01OCT2012 12OCT2012 WORSE

;

run;

proc sql;

create table final as select a.* from new a where event_date=visit_date union select b.event_date,b.visit_date," " as event_type,

b.reason_for_visit from new b where b.event_date<>b.event_date);

quit;

Output:-

Hope this solves your problem

/Daman

Re: insert missing values when condition not satisfied

mano — Tue, 26 Feb 2013 13:41:27 GMT

Thanks for your code .. But the problem is when there are different reasons of visits for the same event, then event_type should be missing for both the visits.

in the above example , event that was started on 04aug2012 has 2 visits (04aug and 10aug)but reasons are different(ARI and ARI-WORSE) ,so event_type would be missing in both the case..

using your code we get event_type missing only in the second visit.

csm you please help me with this.

Thanks..

Re: insert missing values when condition not satisfied

Florent — Tue, 26 Feb 2013 17:00:43 GMT

Hi mano,

Would the following code help ?

Kr,

Florent

proc sql;

create table want as

select n.event_date

, n.visit_date

, CASE

WHEN v1.nbr_visits > 1 THEN ''

ELSE n.reason_for_visit

END as reason_for_visit

from new n

inner join (

select event_date

, count(visit_date) as nbr_visits

from new

group by event_date

) v1

on n.event_date = v1.event_date ;

quit;

Re: insert missing values when condition not satisfied

UrvishShah — Wed, 27 Feb 2013 05:51:59 GMT

Hi Mano,

Sorry i have understood your output from different business logic...So my earlier code that i have posted, works only for the ARI not for the WORSE...So here you go by using DATA Step...

proc sort data = have;

by event_date reason_for_visit;

run;

data want;

set have;

by event_date reason_for_visit;

if first.event_date NE last.event_date AND

first.reason_for_visit EQ last.reason_for_visit then do;

event_type = " ";

end;

if first.reason_for_visit NE last.reason_for_visit then do;

if last.reason_for_visit then event_type = " ";

end;

run;

Thanks,

Urvish

Re: insert missing values when condition not satisfied

mano — Wed, 27 Feb 2013 13:01:17 GMT

Hello Urvish,

Thanks for your code.

if there are only 2 visits for one event, we can use your code stating " first.reason_for_visit NE last.reason_for_visit"..but if there are 3 visits for ex:

ARI 04AUG2012 04AUG2012 ARI

ARI 04AUG2012 10AUG2012 ARI-WORSE

ARI 04AUG2012 16AUG2012 ARI.

i think that statement dont work..

I have modified the code that florent (above) sent to counting disitinct reason_for_visits for same event_type and it worked. Here is the modified code:-

proc sql;

create table want as

select n.event_date

, n.visit_date

, n.reason_for_visit

, CASE

WHEN v1.reasons > 1 THEN ''

ELSE n.event_type

END as event_type

from new n

inner join (

select event_date

, count(distinct reason_for_visit) as reasons

from new

group by event_date

) v1

on n.event_date = v1.event_date ;

quit;

Thanks for all your help.

Re: insert missing values when condition not satisfied

UrvishShah — Wed, 27 Feb 2013 13:43:41 GMT

Thats good that you got your stuff...Actually, i was looking at your Sample Data only...

Re: insert missing values when condition not satisfied

mano — Wed, 27 Feb 2013 15:16:01 GMT

hi florent,

thanks for your help. It was very helpful, I made few changes to your code and got it worked.

you can see the changes I made at the top.

Regards.