Please help me figure this out

[devoidx]

Hi guys so this is my problem, in my database i have a variable called "servicedate" which is the date that a patient visited the doctor, then I also have variables month1 to month12 (for each month of the year) which specifies if a patient was enrolled in a health insurance for each month of the year (1 for yes 0 for no) now what I am trying to do is to select the patients who were enrolled in an insurance plan for the next months following their "servicedate".

for example:

if the patient had a service date of April 15 2010, I only want to select him if he was enrolled in an insurance plan in the next subsequent 3 months of June July and August so he would need to have a value of "1" for variables month6, month7 and month8..

Any ideas how I go about this?

Thanks!

[Richard Ristow]

At 07:58 PM 8/26/2014, devoidx wrote:

>I have a variable called "servicedate" which is the date that a
>patient visited the doctor, then I also have variables month1 to
>month12 (for each month of the year) which specifies if a patient
>was enrolled in a health insurance [in that month]
>
>if the patient had a service date of April 15 2010, I only want to
>select him if he was enrolled in an insurance plan in the next
>subsequent 3 months of June July and August so he would need to have
>a value of "1" for variables month6, month7 and month8..

The 'subsequent three months' to April are May, June, and July, not
June, July and August. Do you really want to select based on
insurance status starting over a month from the visit?

And what do you want to do for visit dates in, say, November, when
you don't have insurance status for the full three or four subsequent months?

Solving your problem as stated isn't very difficult, but I'd like to
raise the above questions first.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[devoidx]

Hi yes, as you said the months would be May June July..had a brain fart!  As for november, you bring up a good point which was going to be my next question but since you brought it up Ill give you more detail about the databases:

my office visit information including the servicedate variable is in a separate database and the insurance coverage information for each year is in a different database so I have a officevisit database and I have a 2008 Insurance coverage database, 2009,2010, etc and within in each insurance coverage database I have a YEAR variable which shows which year the data belongs to and then the month1 to month12 variables that I mentioned.

So for a November 2008 service date, I would need the month11 of 2008 and month1 and month2 of 2009 to have a value of 1.

[David Marso]

So what have you attempted to write yourself thus far?
For a minor clue see MATCH FILES, VECTOR and LOOP!
Other than that you should try sorting this yourself and then post your 'brain fart' code for further scrutiny!
---

devoidx wrote

Hi yes, as you said the months would be May June July..had a brain fart!  As for november, you bring up a good point which was going to be my next question but since you brought it up Ill give you more detail about the databases:

my office visit information including the servicedate variable is in a separate database and the insurance coverage information for each year is in a different database so I have a officevisit database and I have a 2008 Insurance coverage database, 2009,2010, etc and within in each insurance coverage database I have a YEAR variable which shows which year the data belongs to and then the month1 to month12 variables that I mentioned.

So for a November 2008 service date, I would need the month11 of 2008 and month1 and month2 of 2009 to have a value of 1.

[MLIves]

I do not understand exactly what you are trying to do, but based on what you have said, I would recommend that you look at the date functions.
You can create MMYY variables from the month and year and create the same from service date.

DATE.MOYR. DATE.MOYR(month,year). Numeric. Returns a date value corresponding to the indicated month and year. The arguments must resolve to integers, with month between 1 and 13, and year a four-digit integer greater than 1582. To display the result as a date, assign a date format to the result variable.

XDATE.MONTH. XDATE.MONTH(datevalue). Numeric. Returns the month (an integer between 1 and 12) from a numeric value that represents a date. The argument can be a number, a date format variable, or an expression that resolves to a date.

XDATE.YEAR. XDATE.YEAR(datevalue). Numeric. Returns the year (as a four-digit integer) from a numeric value that represents a date. The argument can be a number, a date format variable, or an expression that resolves to a date.

You should be able to use a combination of these calculations, match files and date calculations (DATEDIFF or DATESUM) to get the files together and determine if any insurance date is within 1-3 months of the service date.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of devoidx
Sent: Wednesday, August 27, 2014 9:20 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Please help me figure this out

Hi yes, as you said the months would be May June July..had a brain fart!  As for november, you bring up a good point which was going to be my next question but since you brought it up Ill give you more detail about the
databases:

my office visit information including the servicedate variable is in a separate database and the insurance coverage information for each year is in a different database so I have a officevisit database and I have a 2008 Insurance coverage database, 2009,2010, etc and within in each insurance coverage database I have a YEAR variable which shows which year the data belongs to and then the month1 to month12 variables that I mentioned.

So for a November 2008 service date, I would need the month11 of 2008 and
month1 and month2 of 2009 to have a value of 1.



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Please-help-me-figure-this-out-tp5727095p5727101.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately.  Please notify the sender if you have received this email in error.  NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[devoidx]

Thanks guys for the feedback.

Melissa, Thanks. However, converting the my service date to the month number and matching the files is not the part that I am having troubles with. The part that I can't do is to say if my servicedate month number is 8 then check and see if month9=1 & month10=1 & month11=1.

David, I'll look into vector since I am not very familiar with it. I had the idea of seeing if i can do a SUM and say select if SUM of the next next 3 subsequent months of servicedate is 3 (1+1+1) but I keep getting stuck at picking the next 3 subsequent months of servicedate month

[devoidx]

Hmm I think I figured the vector and loop part i think...Can you take a look David?

string insurance3 (A1).
compute insurance3= "y".
Vector Ins=month1 to month12.
Loop #y=1 to 3.
compute #months= Servicemonth +1.
if (ins(#months) =0) insurance3='n'.
end loop.
execute.

Damn, on the other hand it doesn't look like its adding onto the #months after each loop..it only works for Servicemonth +1 still need to figure out for servicemonth+2 and servicemonth+3

[Art Kendall]

One way to develop an approach is to present some data that covers possibilities of input and that contains variables that I call,e.g.,  'want' or "want1 to want6".

data list list /id (f3) input1 to input4 (4f2), Want1 to want5(5a1).
begin data
1 1 2 3 4 a b c d e
2 4 5 6 7 w x y y y
3 3 9 5 8 l n p o g
end data.

Then work out your efforts at developing syntax.  You would be surprised at how often being explicit about what you are trying to do helps YOU decide how  to draft syntax.

Posting a small set of data that shows explicitly want you want, makes it clearer to potential helpers what you are trying to do.

[devoidx]

Thanks Art, you're right.

I think I finally figured out a way. not sure if this is the prettiest way of doing it but it seems to work. David, thanks for pointing me towards vector and loop.

string insurance3 (A1).
compute insurance3= "y".
Vector Ins=month1 to month12.
Loop #y=1 to 3.
compute #months= Servicemonth + #y.
if (ins(#months) =0) insurance3='n'.
end loop.
execute.

Now next problem is if someone comes in November 2008...I would need to somehow tell spss to look at month12 of 2008 and month1 and month2 of 2009 to look at the next 3 consecutive months especially since 2008 and 2009 insurance information are in seperate databases

[Richard Ristow]

At 09:20 AM 8/27/2014, devoidx wrote:

>my office visit information including the servicedate variable is in
>a separate database and the insurance coverage information for each
>year is in a different database so I have an office visit database
>and I have a 2008 Insurance coverage database, 2009,2010, etc and
>within in each insurance coverage database I have a YEAR variable
>which shows which year the data belongs to and then the month1 to
>month12 variables that I mentioned.
>
>So for a November 2008 service date, [to classify the patient as
>'insured'] I would need the month11 of 2008 and month1 and month2 of
>2009 to have a value of 1.

That gives you a more complicated problem, that can be addressed
several ways. One way is to attach the insurance information for
*all* years, not just the year of service, to each office-visit
record. It would go something like this, but the code is NOT TESTED:

Below, the patients are identified by variable "PtID". The insurance
files must be pre-sorted in ascending order by PtID.

ADD FILES
   /FILE=Insurance2008
   /FILE=Insurance2009
   /FILE=Insurance2010
   /FILE=Insurance2011
   /FILE=Insurance2012
   /FILE=Insurance2013
   /BY PtID Year.


The CASESTOVARS, below, should give you a file with variable PtID
followed by variables month1.2008, month2.2008, ..., month12.2008,
month1.2009, month2.2009, ..., month12.2013 -- the insurance
variables for all months of all years 2008-2013.

CASESTOVARS
   /ID      =PtID
   /INDEX   =Year
   /GROUPBY =Index
   /AUTOFIX =NO.

DATASET NAME     Insurance  WINDOW=FRONT.

RECODE           month1.2008 TO month12.2013 (SYSMIS = 9).
MISSING VALUES   month1.2008 TO month12.2013 (9).
ADD VALUE LABELS month1.2008 TO month12.2013
                  9 'No data'.


Then, similar to what you've probably done previously, combine
office-visit and insurance data. (The office visits must be
pre-sorted by PtID.)

MATCH FILES
    /FILE =OfficeVisits
    /TABLE=Insurance
    /BY PtId.

The final trick is to get the number of the month in which the visit
occurred. This is no longer the calendar month within the year; it is
a sequence number, starting with 1 in January 2008:

NUMERIC #SvcMnth (F4).
COMPUTE #SvcMnth = DATEDIFF(VisitDate,DATE.DMY(1,1,2008),"Months") + 1.

Then you can use pretty much the vector-loop logic you've already worked out.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 12:06 PM 8/27/2014, devoidx wrote:

>string insurance3 (A1).
>compute insurance3= "y".
>Vector Ins=month1 to month12.
>Loop #y=1 to 3.
>compute #months= Servicemonth + #y.
>if (ins(#months) =0) insurance3='n'.
>end loop.

I think this is basically right. However, it will produce warning
messages if Servicemonth is 10, 11, or 12; and be too permissive in
classifying insurance3 as "y" in those cases.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[devoidx]

Thhanks alot Richard, I was actually doing something very similar then saw your post which validated what i was doing..the problem i am having is that in casetovars is that if a patient was first introduced to my database in 2009 (ie. lacking any insurance information in the 2007 database) then when I do casetovars his 2008 insurance data will become the first 12 months of the casetovars which screws up the whole thing.

I can't figure out a way create a dummy 2007 entry for the patients who only got introduced to the databases in 2008 so when I do a casetovars their dummy 12 months of 2007 would be included so all patients would have 2007 as their first 12 months and 2008 as next 12 months

EDIT: never mind the indexing by year in casetovars is what needed to be done which i realized after looking at your code...awesome! thanks again Richard!

[Ryan]

OT: Next time I suggest a better subject line. "Please help me..." is uninformative. This is just a very gentle recommendation.

Sent from my iPhone

> On Aug 28, 2014, at 12:16 AM, devoidx <[hidden email]> wrote:
>
> Thhanks alot Richard, I was actually doing something very similar then saw
> your post which validated what i was doing..the problem i am having is that
> in casetovars is that if a patient was first introduced to my database in
> 2009 (ie. lacking any insurance information in the 2007 database) then when
> I do casetovars his 2008 insurance data will become the first 12 months of
> the casetovars which screws up the whole thing.
>
> I can't figure out a way create a dummy 2007 entry for the patients who only
> got introduced to the databases in 2008 so when I do a casetovars their
> dummy 12 months of 2007 would be included so all patients would have 2007 as
> their first 12 months and 2008 as next 12 months
>
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Please-help-me-figure-this-out-tp5727095p5727110.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 12:16 AM 8/28/2014, devoidx wrote:

>Thanks a lot Richard, I was actually doing something very similar
>then saw your post which validated what i was doing..the problem i
>am having is that in casetovars is that if a patient was first
>introduced to my database in 2009 (ie. lacking any insurance
>information in the 2007 database) then when I do casetovars his 2008
>insurance data will become the first 12 months of the casetovars
>which screws up the whole thing.

The /INDEX= option on CASESTOVARS is supposed to handle that. Here,
again, is the CASESTOVARS I suggested. It assumes there's a variable
"Year" in each insurance record giving the year it's for; you've said
that's the case:

CASESTOVARS
   /ID      =PtID
   /INDEX   =Year
   /GROUPBY =Index
   /AUTOFIX =NO.

What is the *exact* syntax you are using to combine the insurance
files and run CASESTOVARS on them?

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Bruce Weaver]

Richard, I suspect you missed this EDIT to the devoidx post you were responding to (it may not have been visible to people who don't read via Nabble):

http://spssx-discussion.1045642.n5.nabble.com/Please-help-me-figure-this-out-tp5727095p5727110.html

Cheers!  ;-)

Richard Ristow wrote

At 12:16 AM 8/28/2014, devoidx wrote:

>Thanks a lot Richard, I was actually doing something very similar
>then saw your post which validated what i was doing..the problem i
>am having is that in casetovars is that if a patient was first
>introduced to my database in 2009 (ie. lacking any insurance
>information in the 2007 database) then when I do casetovars his 2008
>insurance data will become the first 12 months of the casetovars
>which screws up the whole thing.

The /INDEX= option on CASESTOVARS is supposed to handle that. Here,
again, is the CASESTOVARS I suggested. It assumes there's a variable
"Year" in each insurance record giving the year it's for; you've said
that's the case:

CASESTOVARS
   /ID      =PtID
   /INDEX   =Year
   /GROUPBY =Index
   /AUTOFIX =NO.

What is the *exact* syntax you are using to combine the insurance
files and run CASESTOVARS on them?

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 04:11 PM 8/28/2014, Bruce Weaver wrote:

>Richard, I suspect you missed this EDIT to the devoidx post you were
>responding to (it may not have been visible to people who don't read
>via Nabble):
>
>http://spssx-discussion.1045642.n5.nabble.com/Please-help-me-figure-this-out-tp5727095p5727110.html

I did indeed miss it; thank you. I receive SPSSX-L postings from the
Listserv server at UGA; that picks up new Nabble postings, but not
edits. I'm glad the problem is solved.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD