DATA Step, Macro, Functions and more

Searching long-format data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Searching long-format data

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

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!


Accepted Solutions
Solution
‎04-24-2014 06:10 PM
Super User
Posts: 19,820

Re: Searching long-format data

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.

View solution in original post


All Replies
Super User
Posts: 5,431

Re: Searching long-format data

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?

Data never sleeps
Frequent Contributor
Posts: 82

Re: Searching long-format data

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; 

Super User
Super User
Posts: 7,059

Re: Searching long-format data

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.

Solution
‎04-24-2014 06:10 PM
Super User
Posts: 19,820

Re: Searching long-format data

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.

Frequent Contributor
Posts: 82

Re: Searching long-format data

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 224 views
  • 3 likes
  • 4 in conversation