topic Re: data management with transpose HELP please in SAS Data Management

data management with transpose HELP please

chuie — Mon, 27 Feb 2017 15:17:15 GMT

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

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.

Re: data management with transpose HELP please

PGStats — Tue, 28 Feb 2017 03:12:23 GMT

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;

Re: data management with transpose HELP please

chuie — Tue, 28 Feb 2017 17:32:38 GMT

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.

Re: data management with transpose HELP please

PGStats — Tue, 28 Feb 2017 19:54:35 GMT

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;

Re: data management with transpose HELP please

chuie — Tue, 28 Feb 2017 20:44:34 GMT

You are AWESOME....

Thank you so much

Re: data management with transpose HELP please

chuie — Fri, 31 Mar 2017 17:53:38 GMT

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

Re: data management with transpose HELP please

DavidGhan — Fri, 31 Mar 2017 18:17:56 GMT

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;

Re: data management with transpose HELP please

chuie — Fri, 31 Mar 2017 19:02:28 GMT

This worked as a magic.

Thank you David

Re: data management with transpose HELP please

chuie — Sat, 13 May 2017 19:35:59 GMT

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

Re: data management with transpose HELP please

DavidGhan — Tue, 30 May 2017 15:24:55 GMT

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.

Re: data management with transpose HELP please

chuie — Tue, 08 Aug 2017 16:23:32 GMT

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

Re: data management with transpose HELP please

DavidGhan — Wed, 30 Aug 2017 13:59:59 GMT

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;

Re: data management with transpose HELP please

chuie — Thu, 31 Aug 2017 15:13:56 GMT

David,

You are my "SAS GOD" 🙂

I cannot thank you enough....