flat filing

[monsterpie]

Hi all, 

I have a dataset which includes patients that have all had "X" specific heart condition, with multiple rows per for the same patient id (ptid). Each row is an admission to the hospital. I am trying to count the # of admissions for X (the hear condition) and flatten the file so there is one row per patient id. 

Here is an example of my data currently: 

ADMID PTID   HRTCON     DATE

 6573     01            X            02MAR2003

 4359     01            X            07JUN2004

7893      01            X            12DEC2004

 8989     02            X            05APR2003

 0542     02            X            09MAY2005

What I am trying to end up with as a FINAL dataset:

PTID HRTCON  COUNT

01          X            3

02          X            2

My end goal is to determine the number of patients that were admitted one or more times for this heart condition. Here is the code I've tried so far to flatten the file (but this has not worked to flattened the file according to ptid):

proc means data=admhrt noprint;
class ptid;
types ptid;
output out=heart1 n(ptid)=count;
run;

I have also tried: 

data heart2;
set admhrt;
by ptid;
if first.ptid then count=0;
count=count+1;
if last.ptid then output;
retain count;
run;

[yabwon]

Hi,
start with PROC SQL and aggregation by PTID, e.g.:

select PTID, HRTCON, count(1) as COUNT from DATA where HRTCON = 'X' group by PTID;

All the best
Bart

[monsterpie]

Hi Bart,
Thank you for your help! I will try out this solution. I am also wondering though if there is a way to do this using proc means or the datastep I've showed? I thought both those would work so I'm just wondering if there was something wrong with my code.

[yabwon]

The code you provided looks ok? I'm not sure what is the issue?
All the best
Bart

[Patrick · Accepted solution]

@monsterpie 

For the datastep: Just make sure that you retain the count variable and also only count rows where HRTCON='X'. You could amend your datastep code with one of below options:

/* option 1 */
if HRTCON='X' then count=count+1;

/* option 2 */
count+(HRTCON='X');

[art297]

Are you looking for something like the following?:

data admhrt;
  input ADMID PTID $  HRTCON  $  DATE date9.;
  cards;
6573     01            X            02MAR2003
4359     01            X            07JUN2004
7893     01            X            12DEC2004
7894     01            Y            13dec2004
7895     01            Y            14dec2004
8989     02            X            05APR2003
0542     02            X            09MAY2005
;
run;

proc means data=admhrt (where=(hrtcon eq 'X')) nway noprint;
  class ptid hrtcon;
  output out=heart1 (drop=_:) n=count;
run;

Art, CEO, AnalystFinder.com