coding using dates

[stellapersis7]

Hi all,

I have a dataset this way

ID.   date.                A1.  A2.   A3. A4.    A5.    A6.    A7

1.      01-apr-23       1       1       0       1        1        0         1

2.      02-may-23     1     0       1          1      1       1            1

3      10-jun-23         1       0       0      0      0       0          0

4      21 may-23       1        1        1         1      1        1          1

I want to identify IDs in this way

01-apr-23 to 30 apr-23 -- who have 1 in A1-A6

01-may-23 to 31-may-23 who have 1 in A2-A7

01-jun-23 to 30-jun-23  who have 1 in a1-a4

I tried to use do loop and proc transpose but failed to get the right code. need help please!

Thank you in advance

Appreciate it

[Oligolas]

Hi,

What does the wanted results look like?

Do you want to have the records or the column names?

Do you want the results from the date to end of the month or explicitly only to 30apr23, 31may23, 30jun23?

[stellapersis7]

Sorry for the confusion!

So the  A1-A7 variables can be either 0 or 1 (its like yes/no) 

I don't necessarily need to make a new dataset with those observations but I need just counts

So my research question is:

How many of the IDs between 01-apr-23 to 30 apr-23 have all 1 in A1-A6?

(the results be like 80 IDs out of the 100 IDs present between 01-apr-23 to 30 apr-23 have all 1s in A1-A6)

similarly How many of the IDs between 01-may-23 to 31-may-23 have all 1 in A2-A7?

and How many of the IDs between 01-jun-23 to 30-jun-23 have all 1 in a1-a4?

Thank you

[Kurt_Bremser]

---

@stellapersis7 wrote:

Sorry for the confusion!

So the  A1-A7 variables can be either 0 or 1 (its like yes/no) 

I don't necessarily need to make a new dataset with those observations but I need just counts

So my research question is:

How many of the IDs between 01-apr-23 to 30 apr-23 have all 1 in A1-A6?

(the results be like 80 IDs out of the 100 IDs present between 01-apr-23 to 30 apr-23 have all 1s in A1-A6)

similarly How many of the IDs between 01-may-23 to 31-may-23 have all 1 in A2-A7?

and How many of the IDs between 01-jun-23 to 30-jun-23 have all 1 in a1-a4?

Thank you

---

So how do you know that in April you have to question A1-A6, in May A2-A7, but in June A1-A4? Please explain the rule.

[stellapersis7]

That's my research question,

A's stand for months (jan, feb etc)

So, for those born in April, 3 months prior and 3 months later is their eligibility to choose.

[Kurt_Bremser]

And from where do you get the birth dates? Your variable "date" can't be it, because someone born in June should not check January to April, according to your rule.

Please do not force us to pull information out of your nose little bit by little bit. The quickest way to a solution is a proper question.

And do not repost questions in new threads, it only forces one of use to merge the posts.

[stellapersis7]

needs be you can merge.

thank you

[Tom]

---

@stellapersis7 wrote:

That's my research question,

A's stand for months (jan, feb etc)

So, for those born in April, 3 months prior and 3 months later is their eligibility to choose.

---

Medicare enrollment?  That has a four month window if you count the month of birth.  It really has a 7 month enrollment window since you can enroll up to 3 months before your birthday.

[stellapersis7]

It's not Medicare, but a local similar dataset of enrollments which I cannot disclose. I want to check the continuous enrollment of the individuals 3 months prior and later. The monthly enrollments are given as a table of A's with 1 and 0. I need the sas code to run through all the months and identify individuals who have continuous enrollment period of 3 months prior and 3 months later to the "date". 

[Tom]

You should be able to use SUBSTR() to help.

Say you had a string of length 24 representing the 24 months in 2022 and 2023 and you wanted to see if someone had 3 months of enrollment that included the month of an EVENT and the next two months.

data have;
   input id event :date.  estring $24. ;
   format event date9.;
cards;
1 01MAR2022 011111000000000000000000
2 01MAR2022 111100000000000000000000
3 01DEC2023 111111111111111111111111
;

So just calculate the index representing their event date and then take the string starting at the point and see it if starts with 3 ones to see if there is 3 months of coverage.

data want;
  set have;
  index=1+intck('month','01JAN2022'd,event);
  if index in (1:24) then cover3 = ('111' = substrn(estring,index,3) ) ;
run;

The first case has 3 months coverage.  The second doesn't since it only has March and April.  The last one doesn't since we don't know about the coverage past Dec 2023.

[Tom]

---

@stellapersis7 wrote:

That's my research question,

A's stand for months (jan, feb etc)

So, for those born in April, 3 months prior and 3 months later is their eligibility to choose.

---

I don't think your proposed source data can answer that question.  Where are the dates?  What do the A variables actually mean?

You probably need actual dates.  So you need the date they are eligible to enroll.  If you have DOB you could calculate the enrollment window. So someone that was 65 in April 2023 could have enrolled in the 7 month window between 01JAN2023 and 31JUL2023.  Do you have the actual date they enrolled?  If so you can then test whether or not they enrolled, whether they enrolled in that window, whether they enrolled after that window, whether enrolled before that window (because of meeting some other qualification than just age).

So if you have data like this:

data have;
  input id dob :date. enroll :date.;
  format date enroll date9.;
cards;
1 01APR1958 01JAN2023
2 30JUL1958 01APR2023
;

You can test if they enrolled in the window.

data want;
  set have;
  inwindow = . < abs(intck('month',intnx('year',dob,65),enroll)) <= 3 ;
run;

[Kurt_Bremser]

What is the rule for selecting the variables in which to look for the "1" values?

[Lukkul]

Yeah, the rule is very confusing... 

Do I understand it correctly:

e.g. For dates between 01Apr2023 to 30Apr2023, you want to extract the ID of a row only if there is (at least one) 1 in A1-A6? 
or maybe is it
If there is at least one 1 in A1-A6, check if the date is in correct range between 01Apr2023 to 30Apr2023?

Anyway, make sure you're not working on strings as a date, use informat to read in dates to numeric representation.

[stellapersis7]

Hi all,

I have a dataset this way

ID.   date.                A1.  A2.   A3. A4.    A5.    A6.    A7

1.      01-apr-23       1       1       0       1        1        0         1

2.      02-may-23     1     0       1          1      1       1            1

3      10-jun-23         1       0       0      0      0       0          0

4      21 may-23       1        1        1         1      1        1          1

So the  A1-A7 variables can be either 0 or 1 (its like yes/no) 

I don't necessarily need to make a new dataset with those observations but I need just counts

So my research question is:

How many of the IDs between 01-apr-23 to 30 apr-23 have all 1 in A1-A6?

(the results be like 80 IDs out of the 100 IDs present between 01-apr-23 to 30 apr-23 have all 1s in A1-A6)

similarly How many of the IDs between 01-may-23 to 31-may-23 have all 1 in A2-A7?

and How many of the IDs between 01-jun-23 to 30-jun-23 have all 1 in a1-a4?

I tried to use do loop and proc transpose but failed to get the right code. need help please!

Thank you in advance

Appreciate it