BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mano
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mano
Calcite | Level 5

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.

  

View solution in original post

8 REPLIES 8
UrvishShah
Fluorite | Level 6

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
Obsidian | Level 7

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

Capture.JPG

Hope this solves your problem

/Daman

mano
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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
Fluorite | Level 6

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
Calcite | Level 5

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1170 views
  • 4 likes
  • 4 in conversation