topic Re: Searching long-format data in SAS Programming

Searching long-format data

chuakp — Thu, 24 Apr 2014 21:09:58 GMT

I have a health care discharge dataset where each row represents a lab. There are unique identifiers for the episode of care (DISCHARGE_ID), and each lab that the patient received during that episode of care is listed (the data is in long format, not wide format). There is also a variable I created called "Chemistry" that equals 1 if the patient got a chemistry panel. For example:

DISCHARGE_ID LAB CODE CHEMISTRY

1 12345 1

1 12532 0

2 09343 0

3 34341 0

3 12345 1

I am trying to create code that creates a list of unique discharge_ID's in which one or more chemistry panels was obtained during the episode of care. I can see how I might do this if I transposed the data to wide format, but I'm curious how one would do this without transposing. This is the code I have used:

data chemistry; set lab;

by discharge_id;

if first.discharge_id;

where chemistry = 1;

run;

Does that look right? Thanks!

Re: Searching long-format data

LinusH — Thu, 24 Apr 2014 21:24:06 GMT

You have a problem. You also have some code. But instead of testing it yourself you ask the community. .? Why don't you test it first and then get back if you can't make it work?

Re: Searching long-format data

chuakp — Thu, 24 Apr 2014 21:34:22 GMT

I apologize for any confusion. I did test this code and got some results that look right, but I'm not sure they're what I am looking for. My specific question is whether the use of "if" and "where" statements is appropriate...should it just be one if statement like:

if first.discharge_id and chemistry = 1;

Re: Searching long-format data

Reeza — Thu, 24 Apr 2014 22:10:19 GMT

Given your stated logic your code is not correct.

EDIT: It will work, my bad.

The where clause will keep only records with 1, and then the first will keep only the first record, the unique record. The only issue with your code is the order of the lines. Where statements should be right after the SET statement to make it more understandable.

Re: Searching long-format data

Tom — Thu, 24 Apr 2014 23:01:35 GMT

The two forms are NOT equivalent. The WHERE statement will limit the rows that are brought into the dataset. So ALL rows with have CHEMISTrY=1. Any records with CHEMISTRY=0 will not be seen at all. In the second form only the subjects that have CHEMISTRY=1 on the first record for that subject will be kept. So if someone had CHEMISTRY=0 and then CHEMISTRY=1 it would not be included in the second form and would be included in your original form.

Re: Searching long-format data

chuakp — Thu, 24 Apr 2014 23:44:06 GMT

Thanks Reeza and Tom for the input - sounds like I should stick with the original code.