DATA Step, Macro, Functions and more

SAS coding

Reply
Occasional Contributor
Posts: 7

SAS coding

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.

Occasional Contributor
Posts: 7

Re: SAS coding

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
Respected Advisor
Posts: 2,647

Re: SAS coding

[ Edited ]

 

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
Occasional Contributor
Posts: 7

Re: SAS coding

Posted in reply to PaigeMiller

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.

Respected Advisor
Posts: 2,647

Re: SAS coding

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

--
Paige Miller
Occasional Contributor
Posts: 7

Re: SAS coding

Posted in reply to PaigeMiller

any inputs?

Occasional Contributor
Posts: 7

Re: SAS coding

Posted in reply to PaigeMiller
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

 

Frequent Contributor
Posts: 142

Re: SAS coding

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;

Contributor
Posts: 37

Re: SAS coding

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;

Occasional Contributor
Posts: 7

Re: SAS coding

Posted in reply to srinath3111

i want the ER_count on the encounters level, not ID

Occasional Contributor
Posts: 7

Re: SAS coding

Posted in reply to srinath3111

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

PROC Star
Posts: 8,104

Re: SAS coding

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

 

Ask a Question
Discussion stats
  • 11 replies
  • 159 views
  • 0 likes
  • 5 in conversation