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

Hi

i have a claim data of encounter type of all clinic A users.How I have identified clinic A users is from the encounter type = clinic A. so I would like to count number of hopsitalization, ED,  BEFORE AND AFTER his first encounter(by date) to clinic A. So basically the first visit date to clinic A is the intervention date.In the data set below, ID 1 started using clinic A is from 2/3 so any enconter before that date is considered before and after that date is after.

data is like

ID    dateofvisit     encountertype

1       2/1                       ED

1       2/2                       hospitalization

1      2/3                         clinic A

1      2/4                          ED

2

2

3

etc

The want data is: 

ID       beforeED      Beforehospitalization         afterED            afterhospitalization

1            1                        1                                  1                               0

 

Thank you so much in advance. I have spend 2 weeks to figure out by myself ... any help would be highly appriciated.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Assuming your data is sorted by id and dates:

 

data have;
input ID    dateofvisit :$8.    encountertype &:$20.;
datalines;
1       2/1                       ED
1       2/2                       hospitalization
1      2/3                         clinic A
1      2/4                          ED
2       2/1                       ED
2      2/2                         clinic A
2       2/3                       hospitalization
2      2/4                          ED
;

data temp;
length col $32;
state = "before";
dum = 1;  /* Needed for transpose */
do until (last.id);
    set have; by id;
    if encountertype = "clinic A" then state = "after";
    else do;
        col = cats(state, encountertype);
        output;
        end;
    end;
keep id col dum;
run;

proc transpose data=temp out=almost(drop=_name_);
by id;
var dum;
id col;
run;

/* Replace missing with zeros */
proc stdize data=almost out=want reponly missing=0;
run;
PG

View solution in original post

12 REPLIES 12
PGStats
Opal | Level 21

Assuming your data is sorted by id and dates:

 

data have;
input ID    dateofvisit :$8.    encountertype &:$20.;
datalines;
1       2/1                       ED
1       2/2                       hospitalization
1      2/3                         clinic A
1      2/4                          ED
2       2/1                       ED
2      2/2                         clinic A
2       2/3                       hospitalization
2      2/4                          ED
;

data temp;
length col $32;
state = "before";
dum = 1;  /* Needed for transpose */
do until (last.id);
    set have; by id;
    if encountertype = "clinic A" then state = "after";
    else do;
        col = cats(state, encountertype);
        output;
        end;
    end;
keep id col dum;
run;

proc transpose data=temp out=almost(drop=_name_);
by id;
var dum;
id col;
run;

/* Replace missing with zeros */
proc stdize data=almost out=want reponly missing=0;
run;
PG
chuie
Quartz | Level 8

This is excellent. Thank you so much. You have definately made my day...

I would really appricate if you could explain the steps as data doesnot look as simple as it in this example like i have separate clinic data and ED, hospitalization etc are from claims data.

 

PGStats
Opal | Level 21

Here is a commented version:

 

data have;
input ID    dateofvisit :$8.    encountertype &:$20.;
datalines;
1       2/1                       ED
1       2/2                       hospitalization
1      2/3                         clinic A
1      2/4                          ED
2       2/1                       ED
2      2/2                         clinic A
2       2/3                       hospitalization
2      2/4                          ED
;

/* Data is assumed to be sorted by id and date */
data temp;
length col $32;
/* Define initial state at the beginning of each id, 
   to be changed later when clinic name is detected */
state = "before";
/* Define dummy variable needed for transpose */
dum = 1;
/* Loop on observations from a single id */
do until (last.id);
    set have; by id;
    /* If clinic name is detected, change state from before to after. 
       No output needed in this case */
    if encountertype = "clinic A" then state = "after";
    else do;
        /* Form a future variable name by appending state and encountertype */
        col = cats(state, encountertype);
        output;
        end;
    end;
keep id col dum;
run;

/* Transpose creates the new variables whose names were created in previous step */
proc transpose data=temp out=almost(drop=_name_);
by id;
var dum;
id col;
run;

/* Transpose leaves missing values for variables that were not present 
   for a given id. This final step replaces missing values with zeros */
proc stdize data=almost out=want reponly missing=0;
run;
PG
chuie
Quartz | Level 8

You are AWESOME....

Thank you so much 

chuie
Quartz | Level 8

Hi PG,

I got this error message when i tyirend the code in my actual database. I appricate your input in advance.

 

proc transpose data=temp1 out=almost11;
 by MRN;
 var dum;
 id col;
run;

ERROR: The ID value "afterE" occurs twice in the same BY group.
ERROR: The ID value "afterE" occurs twice in the same BY group.
ERROR: The ID value "afterE" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
MRN=123

DavidGhan
SAS Employee

Hi,

The problem you have is that the values of COL will be used for the variable names in the output dataset produced by PROC TRANSPOSE. If you have duplicate values of COL within any of your BY GROUPS (within all the rows for a unique value of MRN), this produces an error because you cannot have two variables with the same name in the output dataset.

this example might help:

 

data temp;

MRN='a'; col='1'; dum=100; output;

MRN='a'; col='3'; dum=87; output;

MRN='b'; col='1'; dum=234; output;

MRN='b'; col='3'; dum=21; output;

run;

data temp1;

MRN='a'; col='1'; dum=100; output;

MRN='a'; col='3'; dum=87; output;

MRN='a'; col='1'; dum=22; output;

MRN='b'; col='1'; dum=234; output;

MRN='b'; col='3'; dum=21; output;

run;

proc print data=temp;

run;

proc print data=temp1;

run;

proc transpose data=temp out=temptrans;

by MRN;

var dum;

id col;

run;

proc print data=temptrans;

run;

proc transpose data=temp1 out=temp1trans;

by MRN;

var dum;

id col;

run;

 

If you execute this you will see that the first transpose on the data temp works because within each group for MRN values of col are not duplicated. In the dataset temp2 an extra row is added with a duplicate value for COL in the group where MRN='a'. Note in this example that since the first character of ID is numeric, PROC TRANSPOSE adds an '_' character as a prefix when it generates the column names from values of COL.

 

If you want to transpose your data like this you will need to first remove duplicate values for COL within each group of MRN. One way to do this (although it may not be the way you need to do it for your data) is to use PROC SORT, sort on MRN and COL and add the NODUPKEY option to your PROC SORT statement. Then you can run the transpose on the de-duplicated output from PROC SORT:

 

proc sort data=temp1 nodupkey out=tempdedup;

by mrn col;

run;

proc transpose data=tempdedup out=temp1trans;

by MRN;

var dum;

id col;

run;

 

chuie
Quartz | Level 8

This worked as a magic.

Thank you David

chuie
Quartz | Level 8

David,

I would like to count the number of ED after clinic date. In this example we only have one ed encounter so this code worked. But data contains multiple visits to visit after clinic visit so how do I count the number of ED encounter?

Please advise

Thank you

DavidGhan
SAS Employee

can you provide some representative of your data that contains records with multiple values you describe and what you need the result to be for that representative of data? That will help me to understand exacly what you have and what you need to produce as output.

chuie
Quartz | Level 8

Thank you David. Sorry for late reply

 

Objective is to count the numbe ro of ED and hospitalization BEFORE AND AFTER his first visit to clinic A

 

here is my orginal data

ID      Date                  Visit type

1       2/1                       ED
1       2/2                       hospitalization
1      2/3                         clinic A
1      2/4                          ED

1      5/4                          ED

1     6/10                         hospitalization
2       2/1                       ED

2       2/5                       ED
2      2/10                         clinic A
2       2/13                       hospitalization
2      21/4                          ED

 

For ID 1 the visit date of clinic A is 2/3 so count the visit to ED and hospitalization before and after this date like so,

ID     before_ED    before_hospitalization          after_ed             After_hospitalization

1         1                           1                                      2                          1

2          2                          0                                       1                         1

there were 1 ED in 2/1 that is before his viist to clinnic A in 2/3 so it was calcuated to before ED.

 

 

Does it make sence? Please HELP

 

Thanks

 

 

DavidGhan
SAS Employee

Well, it took me a few different steps but I did come up with a solution:

 

data original;

length id 8 date 8 visittype $20;

infile cards dlm=',';

input ID dateval $ visittype $;

year=2017;

month=input(scan(dateval,1, '/'), 2.);

day=input(scan(dateval,2, '/'), 2.);

date = mdy(month,day, year);

format date mmddyy5.;

drop dateval year month day;

cards;

1, 2/1, ED

1, 2/2, hospitalization

1, 2/3, clinic A

1, 2/4, ED

1, 5/4, ED

1, 6/10, hospitalization

2, 2/1, ED

2, 2/5, ED

2, 2/10, clinic A

2, 2/13, hospitalization

2, 4/21, ED

;

run;

proc print data=original;

run;

proc sql;

create table firstdates as

 select id, min(date) as fdate format = mmddyy5.

  from original

   where visittype='clinic A'

     group by id;

create table firstdates2 as

   select o.id, date, fdate, visittype,

                case

         when date <= fdate then 'Before'

         when date > fdate then 'After'

      end as BeforAft,

   cat(calculated beforaft,visittype) as group

      from original as o, firstdates as f

        where o.id=f.id;

quit;

proc sql;

  create table counts as

    select id, group, count(*) as count

      from firstdates2

        where visittype ne 'clinic A'

          group by id, group;

quit;

proc transpose data=counts out=c2(drop=_name_);

  by id;

  var count;

  id group;

run;

proc print data=c2;

run;

chuie
Quartz | Level 8

David,

 You are my "SAS GOD" 🙂

 

I cannot thank you enough....

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 2135 views
  • 2 likes
  • 3 in conversation