topic flat filing in SAS Procedures

flat filing

monsterpie — Sat, 28 Mar 2020 13:59:46 GMT

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;

Re: flat filing

yabwon — Sat, 28 Mar 2020 14:00:40 GMT

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

Re: flat filing

monsterpie — Sat, 28 Mar 2020 14:03:58 GMT

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.

Re: flat filing

yabwon — Sat, 28 Mar 2020 14:52:13 GMT

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

Re: flat filing

art297 — Sat, 28 Mar 2020 15:25:06 GMT

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

Re: flat filing

Patrick — Sun, 29 Mar 2020 01:27:59 GMT

@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');