BookmarkSubscribeRSS Feed
allenye
Calcite | Level 5

Can anyone show me how to accomplish this.

I want to count for each encounter number how many time the patient(ID) have ER list Y in the prior 6 month.

11 REPLIES 11
allenye
Calcite | Level 5
Encounter NumIDDateER
132781910011/2/2017Y
439084310012/8/2017Y
23948010015/4/2017N
4395834510011/7/2018N
39048590310015/4/2018N
32346535410022/5/2017Y
5433243210026/8/2017Y
765765756710029/8/2017N
76869810036/8/2017Y
756746510038/9/2017Y
2346575810039/5/2017N
35134346674100310/9/2018N
34575697100311/8/2018Y
3524341100312/8/2018N
PaigeMiller
Diamond | Level 26

 

proc freq data=mydata(where=(date>='01NOV17'd));
    table encounternum*er;
run;

This assumes that "the last 6 months" begins on 01NOV17, if it is some other date then the change is obvious. 

--
Paige Miller
allenye
Calcite | Level 5

This is not what I wanted. 

What I wanted is for every encounter Number, there is a date.

Check back 6 month from that date by ID and see if there is ER visit which is Y.

PaigeMiller
Diamond | Level 26

Show us an example. The data you provided does not seem to have an example.

--
Paige Miller
allenye
Calcite | Level 5

any inputs?

allenye
Calcite | Level 5
ncounter NumIDDateER
132781910011/2/2017Y
439084310012/8/2017Y
23948010015/4/2017N
4395834510011/7/2018N
39048590310015/4/2018N
32346535410022/5/2017Y
5433243210026/8/2017Y
765765756710029/8/2017N
76869810036/8/2017Y
756746510038/9/2017Y
2346575810039/5/2017N
35134346674100310/9/2018N
34575697100311/8/2018Y
3524341100312/8/2018N

 

In this table, the desire output would be (ER_Count not counting current one):

Encounter NumIDDateERER_Count
132781910011/2/2017Y0
439084310012/8/2017Y1
23948010015/4/2017N2
4395834510011/7/2018N0
39048590310015/4/2018N0
32346535410022/5/2017Y0
5433243210026/8/2017Y1
765765756710029/8/2017N1
76869810036/8/2017Y0
756746510038/9/2017Y1
2346575810039/5/2017N2
35134346674100310/9/2018N0
34575697100311/8/2018Y0
3524341100312/8/2018N1

 

Vish33
Lapis Lazuli | Level 10

Try this.:

 

data _null_;

call symput('dt',put(intnx('day',today(),0),ddmmyy10.));

call symput('dtp6mon',put(intnx('month',today(),-6),ddmmyy10.));

run;

%put dt=&dt.;

%put dtp6mon=&dtp6mon.;

 

proc sql;

select Encounter_Num,ID,count(ER) as ER_Cnt

from tbl_name

group by Encounter_Num,ID

having ER='Y'

and date between "&dtp6mon."d and "&dt."d;

quit;

srinath3111
Quartz | Level 8

data ds;
infile datalines;
input Encounter Id Date ER$;
informat Date anydtdte10.;
format Date mmddyy10.;
datalines;
4390843 1001 2/8/2017 Y
1327819 1001 1/2/2017 Y
239480 1001 5/4/2017 N
43958345 1001 1/7/2018 N
390485903 1001 5/4/2018 N
323465354 1002 2/5/2017 Y
54332432 1002 6/8/2017 Y
7657657567 1002 9/8/2017 N
768698 1003 6/8/2017 Y
7567465 1003 8/9/2017 Y
23465758 1003 9/5/2017 N
35134346674 1003 10/9/2018 N
34575697 1003 11/8/2018 Y
3524341 1003 12/8/2018 N
;
run;

proc sort data=ds;
by id;
run;


proc sql;
select distinct count(Encounter), id from ds where ER='Y' and qtr(date) le 2 group by id;
quit;

allenye
Calcite | Level 5

i want the ER_count on the encounters level, not ID

allenye
Calcite | Level 5

your code only catches the last date with ER ='Y'

I want the output in the exact same format like the original table but adding a column counting the previous 6 month ER visit

art297
Opal | Level 21

I think the following does what you want. It uses the transpose macro ( which you can download from http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset ), but you could use proc transpose to make the file wide (but that would be a bit more complicated). To use the transpose macro, just download it, then run it, then run the following code:

 

data have;
  informat Encounter_Num $10.;
  informat date mmddyy10.;
  format date mmddyy10.;
  input Encounter_Num    ID  Date    ER $;
  cards;
1327819 1001    1/2/2017    Y
4390843 1001    2/8/2017    Y
239480  1001    5/4/2017    N
43958345    1001    1/7/2018    N
390485903   1001    5/4/2018    N
323465354   1002    2/5/2017    Y
54332432    1002    6/8/2017    Y
7657657567  1002    9/8/2017    N
768698  1003    6/8/2017    Y
7567465 1003    8/9/2017    Y
23465758    1003    9/5/2017    N
35134346674 1003    10/9/2018   N
34575697    1003    11/8/2018   Y
3524341 1003    12/8/2018   N
;

%transpose(data=have, out=need, by=id, Guessingrows=1000,
  delimiter=_, var=Encounter_Num Date ER)

data want (keep=Encounter_Num ID Date ER ER_Count);
  set need;
  array AEncounter_Num(*) $ Encounter_Num:;
  array ADate(*) Date:;
  array AER(*) $ ER:;
  format date mmddyy10.;
  do lastrec=1 to (dim(AEncounter_Num)-1);
    if missing(AEncounter_Num(lastrec+1)) then leave;
  end;
  do i=1 to lastrec;
    ER_Count=0;
    if i gt 1 then do;
      do j=1 to (i-1);
        if ADate(j) ge intnx('month',ADate(i),-6,'s') and AER(j) eq 'Y' then ER_Count+1;
      end;
    end;
    Encounter_Num=AEncounter_Num(i);
    Date=ADate(i);
    ER=AER(i);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1216 views
  • 0 likes
  • 5 in conversation