insert missing values when condition not satisfied

[mano]

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.

[UrvishShah]

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

[damanaulakh88]

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

[mano]

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

[Florent]

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;

[mano]

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.

[UrvishShah]

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

[mano · Accepted solution]

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.

[UrvishShah]

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