Time Interval Variables

[Thompson82]

I need to create a time interval variable that counts weeks for my selldate variable in 2 year intervals. The weeknum variables in the example data are what I am trying to achieve in sas. 

Parameters

1year intervals = July1-Jun30

year 2023 interval start date=July 1, 2022

year 2023 interval end date=June 30, 2024

sell dates are every Tuesday

I am also looking to create a variable that identifies this week or point in time for past and future years using the max selldate.  I can do this with a series of where statements but am looking for something with less code and more automatic. I want this variable to be for each year, 12 months July-June.  The current variable in the example is what I am trying to achieve in sas.

I appreciate the help. 

DATA dates;
INPUT year selldate :date9. weeknum2023 weeknum2024 weeknum2025 current;
FORMAT selldate date9.;
DATALINES;
2023	05Jul2022	1	.		y
2023	12Jul2022	2	.	.	y
2023	19Jul2022	3	.	.	y
2023	26Jul2022	4	.	.	y
2023	02Aug2022	5	.	.	y
2023	09Aug2022	6	.	.	y
2023	16Aug2022	7	.	.	y
2023	23Aug2022	8	.	.	y
2023	30Aug2022	9	.	.	y
2023	06Sep2022	10	.	.	y
2023	13Sep2022	11	.	.	y
2023	20Sep2022	12	.	.	y
2023	27Sep2022	13	.	.	y
2023	04Oct2022	14	.	.	y
2023	11Oct2022	15	.	.	y
2023	18Oct2022	16	.	.	y
2023	25Oct2022	17	.	.	y
2023	01Nov2022	18	.	.	y
2023	08Nov2022	19	.	.	y
2023	15Nov2022	20	.	.	y
2023	22Nov2022	21	.	.	y
2023	29Nov2022	22	.	.	y
2023	06Dec2022	23	.	.	y
2023	13Dec2022	24	.	.	y
2023	20Dec2022	25	.	.	y
2023	27Dec2022	26	.	.	y
2023	03Jan2023	27	.	.	y
2023	10Jan2023	28	.	.	y
2023	17Jan2023	29	.	.	y
2023	24Jan2023	30	.	.	y
2023	31Jan2023	31	.	.	y
2023	07Feb2023	32	.	.	y
2023	14Feb2023	33	.	.	n
2023	21Feb2023	34	.	.	n
2023	28Feb2023	35	.	.	n
2023	07Mar2023	36	.	.	n
2023	14Mar2023	37	.	.	n
2023	21Mar2023	38	.	.	n
2023	28Mar2023	39	.	.	n
2023	04Apr2023	40	.	.	n
2023	11Apr2023	41	.	.	n
2023	18Apr2023	42	.	.	n
2023	25Apr2023	43	.	.	n
2023	02May2023	44	.	.	n
2023	09May2023	45	.	.	n
2023	16May2023	46	.	.	n
2023	23May2023	47	.	.	n
2023	30May2023	48	.	.	n
2023	06Jun2023	49	.	.	n
2023	13Jun2023	50	.	.	n
2023	20Jun2023	51	.	.	n
2023	27Jun2023	52	.	.	n
2024	04Jul2023	53	1	.	y
2024	11Jul2023	54	2	.	y
2024	18Jul2023	55	3	.	y
2024	25Jul2023	56	4	.	y
2024	01Aug2023	57	5	.	y
2024	08Aug2023	58	6	.	y
2024	15Aug2023	59	7	.	y
2024	22Aug2023	60	8	.	y
2024	29Aug2023	61	9	.	y
2024	05Sep2023	62	10	.	y
2024	12Sep2023	63	11	.	y
2024	19Sep2023	64	12	.	y
2024	26Sep2023	65	13	.	y
2024	03Oct2023	66	14	.	y
2024	10Oct2023	67	15	.	y
2024	17Oct2023	68	16	.	y
2024	24Oct2023	69	17	.	y
2024	31Oct2023	70	18	.	y
2024	07Nov2023	71	19	.	y
2024	14Nov2023	72	20	.	y
2024	21Nov2023	73	21	.	y
2024	28Nov2023	74	22	.	y
2024	05Dec2023	75	23	.	y
2024	12Dec2023	76	24	.	y
2024	19Dec2023	77	25	.	y
2024	26Dec2023	78	26	.	y
2024	02Jan2024	79	27	.	y
2024	09Jan2024	80	28	.	y
2024	16Jan2024	81	29	.	y
2024	23Jan2024	82	30	.	y
2024	30Jan2024	83	31	.	y
2024	06Feb2024	84	32	.	y
2024	13Feb2024	85	33	.	n
2024	20Feb2024	86	34	.	n
2024	27Feb2024	87	35	.	n
2024	05Mar2024	88	36	.	n
2024	12Mar2024	89	37	.	n
2024	19Mar2024	90	38	.	n
2024	26Mar2024	91	39	.	n
2024	02Apr2024	92	40	.	n
2024	09Apr2024	93	41	.	n
2024	16Apr2024	94	42	.	n
2024	23Apr2024	95	43	.	n
2024	30Apr2024	96	44	.	n
2024	07May2024	97	45	.	n
2024	14May2024	98	46	.	n
2024	21May2024	99	47	.	n
2024	28May2024	100	48	.	n
2024	04Jun2024	101	49	.	n
2024	11Jun2024	102	50	.	n
2024	18Jun2024	103	51	.	n
2024	25Jun2024	104	52	.	n
2025	02Jul2024	.	53	1	y
2025	09Jul2024	.	54	2	y
2025	16Jul2024	.	55	3	y
2025	23Jul2024	.	56	4	y
2025	30Jul2024	.	57	5	y
2025	06Aug2024	.	58	6	y
2025	13Aug2024	.	59	7	y
2025	20Aug2024	.	60	8	y
2025	27Aug2024	.	61	9	y
2025	03Sep2024	.	62	10	y
2025	10Sep2024	.	63	11	y
2025	17Sep2024	.	64	12	y
2025	24Sep2024	.	65	13	y
2025	01Oct2024	.	66	14	y
2025	08Oct2024	.	67	15	y
2025	15Oct2024	.	68	16	y
2025	22Oct2024	.	69	17	y
2025	29Oct2024	.	70	18	y
2025	05Nov2024	.	71	19	y
2025	12Nov2024	.	72	20	y
2025	19Nov2024	.	73	21	y
2025	26Nov2024	.	74	22	y
2025	03Dec2024	.	75	23	y
2025	10Dec2024	.	76	24	y
2025	17Dec2024	.	77	25	y
2025	24Dec2024	.	78	26	y
2025	31Dec2024	.	79	27	y
2025	07Jan2025	.	80	28	y
2025	14Jan2025	.	81	29	y
2025	21Jan2025	.	82	30	y
2025	28Jan2025	.	83	31	y
2025	04Feb2025	.	84	32	y
2025	11Feb2025	.	85	33	y

;
RUN;

[PaigeMiller]

I'm lost. I can't figure out what the end result is that you want. I assume its a data set (although you haven't even made that clear), what does this data set look like? SHOW US. And what does the input data set look like? Are we supposed to assume that part of the data set that you show is input and part is output?

[Tom]

Should be able to use INTCK() and NWKDOM() functions.

Let's just do it for 2 "fiscal" years.

data have ;
  input year selldate :date. expect2023 expect2024 ;
  format selldate date9.;
datalines;
2023  05Jul2022 1   .
2023  12Jul2022 2   .
2023  27Dec2022 26  .
2023  03Jan2023 27  .
2023  10Jan2023 28  .
2023  20Jun2023 51  .
2023  27Jun2023 52  . 
2024  04Jul2023 53  1 
2024  11Jul2023 54  2 
2024  18Jul2023 55  3 
2024  19Dec2023 77  25  
2024  26Dec2023 78  26
2024  02Jan2024 79  27
2024  09Jan2024 80  28
2024  18Jun2024 103 51
2024  25Jun2024 104 52
2025  02Jul2024   . 53
2025  09Jul2024   . 54
2025  16Jul2024   . 55
; 

data want;
  set have;
  array weeknum[2023:2024] weeknum2023-weeknum2024;
  do fy=2023 to 2024;
    if NWKDOM( 1, 3, 7, fy-1) <= selldate <= NWKDOM( 1, 3, 7, fy+1)-1
    then weeknum[fy] = 1+intck('week.3',NWKDOM( 1, 3, 7, fy-1),selldate);
  end;
  drop fy;
run;

[Tom]

A more realistic method might be to make a list of date ranges for each year.

data years;
  do year=2022 to 2025;
    start=nwkdom(1,3,7,year-1);
    end=nwkdom(1,3,7,year+1)-1;
    output;
  end;
  format start end date9.;
run;

Then you just need to join and transpose.

proc sql;
  create table tall as
  select a.selldate,b.year,1+intck('week.3',b.start,a.selldate) as weeknum
  from have a
  left join years b 
  on a.selldate between b.start and b.end
  group by a.selldate
  order by a.selldate, b.year
 ;
quit;
proc transpose data=tall out=want(drop=_name_) prefix=weeknum;
  by selldate;
  id year;
  var weeknum;
run;

Result

[Ksharp]

DATA dates;
INPUT year selldate :date9. ;
FORMAT selldate date9.;
DATALINES;
2023	05Jul2022	1	.		y
2023	12Jul2022	2	.	.	y
2023	19Jul2022	3	.	.	y
2023	26Jul2022	4	.	.	y
2023	02Aug2022	5	.	.	y
2023	09Aug2022	6	.	.	y
2023	16Aug2022	7	.	.	y
2023	23Aug2022	8	.	.	y
2023	30Aug2022	9	.	.	y
2023	06Sep2022	10	.	.	y
2023	13Sep2022	11	.	.	y
2023	20Sep2022	12	.	.	y
2023	27Sep2022	13	.	.	y
2023	04Oct2022	14	.	.	y
2023	11Oct2022	15	.	.	y
2023	18Oct2022	16	.	.	y
2023	25Oct2022	17	.	.	y
2023	01Nov2022	18	.	.	y
2023	08Nov2022	19	.	.	y
2023	15Nov2022	20	.	.	y
2023	22Nov2022	21	.	.	y
2023	29Nov2022	22	.	.	y
2023	06Dec2022	23	.	.	y
2023	13Dec2022	24	.	.	y
2023	20Dec2022	25	.	.	y
2023	27Dec2022	26	.	.	y
2023	03Jan2023	27	.	.	y
2023	10Jan2023	28	.	.	y
2023	17Jan2023	29	.	.	y
2023	24Jan2023	30	.	.	y
2023	31Jan2023	31	.	.	y
2023	07Feb2023	32	.	.	y
2023	14Feb2023	33	.	.	n
2023	21Feb2023	34	.	.	n
2023	28Feb2023	35	.	.	n
2023	07Mar2023	36	.	.	n
2023	14Mar2023	37	.	.	n
2023	21Mar2023	38	.	.	n
2023	28Mar2023	39	.	.	n
2023	04Apr2023	40	.	.	n
2023	11Apr2023	41	.	.	n
2023	18Apr2023	42	.	.	n
2023	25Apr2023	43	.	.	n
2023	02May2023	44	.	.	n
2023	09May2023	45	.	.	n
2023	16May2023	46	.	.	n
2023	23May2023	47	.	.	n
2023	30May2023	48	.	.	n
2023	06Jun2023	49	.	.	n
2023	13Jun2023	50	.	.	n
2023	20Jun2023	51	.	.	n
2023	27Jun2023	52	.	.	n
2024	04Jul2023	53	1	.	y
2024	11Jul2023	54	2	.	y
2024	18Jul2023	55	3	.	y
2024	25Jul2023	56	4	.	y
2024	01Aug2023	57	5	.	y
2024	08Aug2023	58	6	.	y
2024	15Aug2023	59	7	.	y
2024	22Aug2023	60	8	.	y
2024	29Aug2023	61	9	.	y
2024	05Sep2023	62	10	.	y
2024	12Sep2023	63	11	.	y
2024	19Sep2023	64	12	.	y
2024	26Sep2023	65	13	.	y
2024	03Oct2023	66	14	.	y
2024	10Oct2023	67	15	.	y
2024	17Oct2023	68	16	.	y
2024	24Oct2023	69	17	.	y
2024	31Oct2023	70	18	.	y
2024	07Nov2023	71	19	.	y
2024	14Nov2023	72	20	.	y
2024	21Nov2023	73	21	.	y
2024	28Nov2023	74	22	.	y
2024	05Dec2023	75	23	.	y
2024	12Dec2023	76	24	.	y
2024	19Dec2023	77	25	.	y
2024	26Dec2023	78	26	.	y
2024	02Jan2024	79	27	.	y
2024	09Jan2024	80	28	.	y
2024	16Jan2024	81	29	.	y
2024	23Jan2024	82	30	.	y
2024	30Jan2024	83	31	.	y
2024	06Feb2024	84	32	.	y
2024	13Feb2024	85	33	.	n
2024	20Feb2024	86	34	.	n
2024	27Feb2024	87	35	.	n
2024	05Mar2024	88	36	.	n
2024	12Mar2024	89	37	.	n
2024	19Mar2024	90	38	.	n
2024	26Mar2024	91	39	.	n
2024	02Apr2024	92	40	.	n
2024	09Apr2024	93	41	.	n
2024	16Apr2024	94	42	.	n
2024	23Apr2024	95	43	.	n
2024	30Apr2024	96	44	.	n
2024	07May2024	97	45	.	n
2024	14May2024	98	46	.	n
2024	21May2024	99	47	.	n
2024	28May2024	100	48	.	n
2024	04Jun2024	101	49	.	n
2024	11Jun2024	102	50	.	n
2024	18Jun2024	103	51	.	n
2024	25Jun2024	104	52	.	n
2025	02Jul2024	.	53	1	y
2025	09Jul2024	.	54	2	y
2025	16Jul2024	.	55	3	y
2025	23Jul2024	.	56	4	y
2025	30Jul2024	.	57	5	y
2025	06Aug2024	.	58	6	y
2025	13Aug2024	.	59	7	y
2025	20Aug2024	.	60	8	y
2025	27Aug2024	.	61	9	y
2025	03Sep2024	.	62	10	y
2025	10Sep2024	.	63	11	y
2025	17Sep2024	.	64	12	y
2025	24Sep2024	.	65	13	y
2025	01Oct2024	.	66	14	y
2025	08Oct2024	.	67	15	y
2025	15Oct2024	.	68	16	y
2025	22Oct2024	.	69	17	y
2025	29Oct2024	.	70	18	y
2025	05Nov2024	.	71	19	y
2025	12Nov2024	.	72	20	y
2025	19Nov2024	.	73	21	y
2025	26Nov2024	.	74	22	y
2025	03Dec2024	.	75	23	y
2025	10Dec2024	.	76	24	y
2025	17Dec2024	.	77	25	y
2025	24Dec2024	.	78	26	y
2025	31Dec2024	.	79	27	y
2025	07Jan2025	.	80	28	y
2025	14Jan2025	.	81	29	y
2025	21Jan2025	.	82	30	y
2025	28Jan2025	.	83	31	y
2025	04Feb2025	.	84	32	y
2025	11Feb2025	.	85	33	y
;
data temp;
 set dates;
 selldate_year=year(intnx('year.7',selldate,0,'b'));
 if selldate_year in (2022 2023) then flag_2022_2023=1;
 if selldate_year in (2023 2024) then flag_2023_2024=1;
 if selldate_year in (2024 2025) then flag_2024_2025=1;
run;
data want;
 set temp;
 if flag_2022_2023=1 then week_2023+1;
  else week_2023=.;
 if flag_2023_2024=1 then week_2024+1;
  else week_2024=.;
 if flag_2024_2025=1 then week_2025+1;
  else week_2025=.;
drop flag_:;
run;

P.S.

Assuming there are not gap among weeks.

[ballardw]

---

@Thompson82 wrote:

I need to create a time interval variable that counts weeks for my selldate variable in 2 year intervals. The weeknum variables in the example data are what I am trying to achieve in sas. 

Parameters

---

When working with "time" and "date" values you want to be very careful about terminology. Your values are dates. Dates are measured in days. Time, and datetime, values in SAS are measured in seconds. If you confuse the two, which happens fairly often with beginners, results are sometimes quite odd.