topic Re: General SAS Question in SAS Procedures

General SAS Question

robertrao — Mon, 12 Nov 2012 21:16:16 GMT

Hi Team,

I have a dataset as shown below..

ID Diag ReadmitID

A 4 A'

B 3 B'

C 5 C'

A' 4

B' 4

C' 6

where A B C are the id of the patient and A' B' and C' are the ID of the same patients during readmission.

Our goal is to identify if he has the same Diag code as that of admission (unprimed ID's) during readmission (primed ID's)

Regards

Re: General SAS Question

art297 — Mon, 12 Nov 2012 21:53:37 GMT

Karun: Someone else will have to show you how this can be stated as a single proc sql call, but I think that the following does what you want:

proc sql;

create table first as

select *

from have

where index(id,"'")=0

;

create table second as

select id as ReadmitID, diag as diag2

from have

where index(id,"'") ne 0

;

create table want as

select a.id,a.diag,a.ReadmitID,b.diag2,

case

when diag=diag2 then "Yes"

else "No"

end as same

from first as a

left join

second as b

on a.ReadmitID=b.ReadmitID

;

quit;

Re: General SAS Question

robertrao — Mon, 12 Nov 2012 22:05:41 GMT

Thanks a ton.

I am not looking for a single SQL call. Simple datastep also could be of great help

Regards

Re: General SAS Question

Astounding — Mon, 12 Nov 2012 22:14:12 GMT

OK, a simple data step. I'm not sure how efficient it will be, but it will work. I added a few more variables that you might (or might not) want.

data want;

set have (keep=ID Diag) nobs=_nobs_;

do _i_=1 to _nobs_;

set have (keep=ReAdmitID Diag rename=(Diag=ReAdmitDiag)) point=_i_;

if (ID=ReAdmitID) and (_n_ ne _i_) then do;

original_observation=_n_;

matching_observation=_i_;

if Diag=ReadmitDiag then same_diagnosis='Y';

else same_diagnosis='N';

output;

end;

run;

OK, maybe it's not really that simple but it's not terrible either.

Re: General SAS Question

robertrao — Mon, 12 Nov 2012 22:25:04 GMT

Hi,

I got the logic but not clear enough.

Could you explain me how can we assign _i_ to a matching observation??

Thanks

Re: General SAS Question

art297 — Mon, 12 Nov 2012 22:25:35 GMT

Maybe you would still consider this one simple:

data have;

input ID $ Diag ReadmitID $;

cards;

A 4 A'

B 3 B'

C 5 C'

A' 4 .

B' 4 .

C' 6 .

;

data first;

set have (where=(index(id,"'")=0));

run;

proc sort data=first;

by ReadmitID;

run;

data second;

set have (drop=ReadmitID

rename=(ID=ReadmitID

diag=diag2)

where=(index(ReadmitID,"'")));

run;

proc sort data=second;

by ReadmitID;

run;

data want;

merge first second;

by ReadmitID;

if diag eq diag2 then same="Yes";

else same="No";

run;

Re: General SAS Question

Haikuo — Mon, 12 Nov 2012 22:37:29 GMT

How about a little Hash() approach:

data have;

infile cards truncover;

input (ID Diag ReadmitID) (:$);

cards;

A 4 A'

B 3 B'

C 5 C'

A' 4

B' 4

C' 6

;

data want;

if _n_=1 then do;

if 0 then set have (rename=(diag=_diag id=_id) keep=id diag);

dcl hash h(dataset:"have(rename=(diag=_diag id=_id) keep=id diag)");

h.definekey('_ID');

h.definedata(all:'y');

h.definedone();

end;

set have;

if h.find(key:ReadmitID)=0 then flag=(diag=_diag);

drop _:;

run;

proc print;run;

Haikuo

Re: General SAS Question

RichardinOz — Mon, 12 Nov 2012 22:41:14 GMT

If you are only interested in readmissions, and there can only be a single readmission per patient, then this will do it in one pass

Proc SQL ;

Create table readmit as

Select adm.ID

, adm.Diag

, adm.ReadmitID

, rad.Diag As ReadmitDiag

, case (rad.Diag)

When (adm.Diag)

Then 'Y'

Else 'N'

End

As Match

From admit adm

, admit rad

Where rad.ID = adm.ReadmitID

;

Quit ;

However, if you want a more general solution where the possibility of multiple readmissions exists then it would be better to recode the readmission ID to match the original admission, then process the resulting dataset for non matches. If this is the case, I could suggest some code.

Richard in Oz

Re: General SAS Question

Astounding — Mon, 12 Nov 2012 23:38:00 GMT

karun,

The second SET statement reads all the observations, and compares each to the observation read by the first SET statement.

The observation read by the first SET statement matches observation # _n_. The observation read by the second SET statement matches observation # _i_, since that second SET statement uses POINT=_i_ to control which observation to read.

Lots of interesting solutions here.

Good luck.

Re: General SAS Question

Ksharp — Tue, 13 Nov 2012 05:39:44 GMT

HaiKuo's code maybe is the fastest way.Here is another SQL solution.

Data is from ArthurT.

data have;
  input ID $  Diag   ReadmitID $;
  cards;
A     4            A'
B     3            B'
C     5           C'
A'     4 .
B'     4 .
C'     6 .
;
run;
proc sql;
 create table want as
 select h.*,case when h.diag eq (select diag from have where id eq h.ReadmitID) then 'Yes'
               when h.diag ne (select diag from have where id eq h.ReadmitID) then 'No'
          else 'No matched' end as flag length=10
  from have as h
;
quit;

Ksharp

Re: General SAS Question

robertrao — Tue, 13 Nov 2012 14:45:32 GMT

Hi Team,

I am trying to execte my problem with all the different solutions.

I understood Art,s logic.

Could you explain to me why should we write (_n_ ne _i_) in the below if statement.....

data want;

set have (keep=ID Diag) nobs=_nobs_;

do _i_=1 to _nobs_;

set have (keep=ReAdmitID Diag rename=(Diag=ReAdmitDiag)) point=_i_;

if (ID=ReAdmitID) and (_n_ ne _i_) then do;

original_observation=_n_;

matching_observation=_i_;

if Diag=ReadmitDiag then same_diagnosis='Y';

else same_diagnosis='N';

output;

end;

run;

Also if someone could explain me HAi's Hash logic..that would be great tooo..

For example..

h.definekey('_ID');

what is h.

is it a library

Thanks

Re: General SAS Question

Astounding — Tue, 13 Nov 2012 14:55:25 GMT

For this particular problem, it's overkill to check:

(_n_ ne _i_)

Basically, it prevents an observation from matching itself. For some applications that's important, but for this one it won't make a difference. The readmission ID would never match the original ID on the same observation.

Re: General SAS Question

robertrao — Tue, 13 Nov 2012 14:58:27 GMT

Exactly,

Thats the reason I was double checking.

Also I was wondering this could be done using PRoc Format CNTLIN??

Thanks

Re: General SAS Question

Astounding — Tue, 13 Nov 2012 15:09:41 GMT

If the ID variable never has duplicates, it would be relatively straightforward to use PROC FORMAT. But that wasn't clear from the limited amount of data. Must ID take on unique values?

Re: General SAS Question

robertrao — Tue, 13 Nov 2012 15:22:40 GMT

YES....The ID variable is Unique.

But using PROC FORMAT i only know until extractling ReadmitIds from the ID variable using CNTLIN.

But I am not sure of the second part...checking to see if the readmits have the same DIAG as the beginning of admission

Regards

Re: General SAS Question

Astounding — Tue, 13 Nov 2012 15:31:20 GMT

OK. I won't be able to work on this for a few hours, but I'd be happy to see anyone else code it. The basic idea is that the CNTLIN data set translates ID into the observation number where that ID occurs. Then march through the observations, and check in a DATA step whether the readmission ID appears in the format. If so, use SET with POINT= to retrieve the matching observation. Along the way, KEEP/RENAME variables as appropriate.

If nobody attempts this, I can get to it this afternoon.

Good luck.

Re: General SAS Question

robertrao — Tue, 13 Nov 2012 15:35:25 GMT

Great Help,

Thanks a ton

Re: General SAS Question

art297 — Tue, 13 Nov 2012 15:44:47 GMT

data have;

input ID $ Diag ReadmitID $;

cards;

A 4 A'

B 3 B'

C 5 C'

A' 4 .

B' 4 .

C' 6 .

;

DATA fmtDataset;

set have (drop=ReadmitID rename=(id=start diag=label));

if index(start,"'");

retain fmtname '$idcode' type 'C';

RUN;

PROC FORMAT CNTLIN=fmtDataset;

RUN;

DATA want;

set have;

if not index(id,"'");

if put(ReadmitID,$idcode.) eq diag then same='yes';

else same='no';

RUN;

Re: General SAS Question

Astounding — Tue, 13 Nov 2012 15:47:36 GMT

Art,

That's a much better idea ... just use the format to translate from ID into Diag.

Re: General SAS Question

robertrao — Tue, 13 Nov 2012 15:59:06 GMT

Thans a ton for your valuable time and effort.

I am curious. In the future if the alphabets are replaced by numbers i can use the same code without the below if conditions in the code

IT STILL WORKS RIGHT???

if index(start,"'");

if not index(id,"'");

data have;

input ID $ Diag ReadmitID $;

cards;

101 4 111

102 3 222

103 5 333

111 4 .

222 4 .

333 6 .

;