SAS coding

[allenye]

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.

[allenye]

Encounter Num	ID	Date	ER
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

[PaigeMiller]

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. 

[allenye]

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]

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

[allenye]

any inputs?

[allenye]

ncounter Num	ID	Date	ER
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

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

Encounter Num	ID	Date	ER	ER_Count
1327819	1001	1/2/2017	Y	0
4390843	1001	2/8/2017	Y	1
239480	1001	5/4/2017	N	2
43958345	1001	1/7/2018	N	0
390485903	1001	5/4/2018	N	0
323465354	1002	2/5/2017	Y	0
54332432	1002	6/8/2017	Y	1
7657657567	1002	9/8/2017	N	1
768698	1003	6/8/2017	Y	0
7567465	1003	8/9/2017	Y	1
23465758	1003	9/5/2017	N	2
35134346674	1003	10/9/2018	N	0
34575697	1003	11/8/2018	Y	0
3524341	1003	12/8/2018	N	1

[Vish33]

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]

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]

i want the ER_count on the encounters level, not ID

[allenye]

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]

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