Obsidian | Level 7

## Extract last 5 weeks data

Hello community,

I have a dataset available at a daily level, with the date column called 'TX_DT' in the format yyyy-mm-dd. I want to limit the data pull to only last 5 weeks. Irrespective of the current date, I would want to extract the data for its previous 5 weeks. For example, if today is 06-29-2023, I want the data from 05-22-2023 until 06-25-2023. In the data my week starts from Monday and ends on Sunday. Similarly, if today was 07-04-2023, I would like the data from 05-29-2023 until 07-02-2023.

I tried using intnx function with the code below but I'm not getting any result.

`data final;    set temp(where=(intnx('dtweek', TX_DT, -5, 'B') = intnx('dtweek', datetime(), 0, 'B')));run; `

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Extract last 5 weeks data

So you have a DATE values.  You will want to use the WEEK interval (or probably the WEEK.1 interval to start on Monday instead of Sunday)  You will want to use the DATE() function (or it's alias TODAY()) to get the current date.

So if you want the data that is newer than 5 weeks ago.

``````data final;
set temp;
where intnx('week.1', today(), -5, 'B') <= TX_DT ;
run; ``````
13 REPLIES 13
Diamond | Level 26

## Re: Extract last 5 weeks data

You want to extract data from between two dates, but nothing you have written checks to see if the date is between these two dates. You don't show your data, so perhaps this is what you want (but really I'm guessing with seeing your data)

``````data final;
set temp(where=(intnx('week.1', TX_DT, -5, 'B') <= tx_dt <= intnx('week.1', datetime(), 0, 'B')));
run; ``````

Note that I am using 'week.1' because you said you want weeks to start on Mondays.

--
Paige Miller
Obsidian | Level 7

## Re: Extract last 5 weeks data

Hi @PaigeMiller, the TX_DT variable is a numeric data type in the dataset which is displayed as '2023-05-22' and so on. When I tried your suggested code, it is not producing any records. Could you please suggest me any corrections?

Super User

## Re: Extract last 5 weeks data

A number cannot display with dashes in it unless there is a FORMAT attached to the variable.

Run PROC CONTENTS on the dataset and see what format specification is attached to the variable.

Obsidian | Level 7

## Re: Extract last 5 weeks data

Okay, thanks for clarifying it. I checked it and the 'Type' shows as Num with its 'Format' as YYMMDD10.
Super User

## Re: Extract last 5 weeks data

So you have a DATE values.  You will want to use the WEEK interval (or probably the WEEK.1 interval to start on Monday instead of Sunday)  You will want to use the DATE() function (or it's alias TODAY()) to get the current date.

So if you want the data that is newer than 5 weeks ago.

``````data final;
set temp;
where intnx('week.1', today(), -5, 'B') <= TX_DT ;
run; ``````
Obsidian | Level 7

## Re: Extract last 5 weeks data

Thank you so much! Just wanted to quickly understand the 'week.1' part as the above code is extracting the values from 05-21-2023 (Sunday) instead of 05-22-2023 (Monday) and when I changed it 'week.2' in the code, it is extracting the values from 05-22-2023 (Monday). Why would this happen?
Super User

## Re: Extract last 5 weeks data

Sunday is day 1.  So week.1 and week are the same thing.

Diamond | Level 26

## Re: Extract last 5 weeks data

@hk24 wrote:

Hi @PaigeMiller, the TX_DT variable is a numeric data type in the dataset which is displayed as '2023-05-22' and so on. When I tried your suggested code, it is not producing any records. Could you please suggest me any corrections?

As I said earlier, I am guessing because you have not shown us the data. It is ALWAYS a good idea to show us a portion of the data following these instructions and examples. Next time, @hk24 , we will expect to see a portion of the data as described. Do not make us ask again.

Please run PROC CONTENTS and report what it says for TX_DT.

--
Paige Miller
Super User

## Re: Extract last 5 weeks data

Why are you using DTWEEK interval if your variable is a DATE?

Is your TX_DT variable really a DATETIME variable instead of a DATE variable?  What format does it have attached to it?

Obsidian | Level 7

## Re: Extract last 5 weeks data

Hi @Tom, when I checked the data type of TX_DT variable, it seems to be numeric and neither date nor datetime data type. Could you please suggest me a solution?

Super User

## Re: Extract last 5 weeks data

If it has no format attached then what actual values does it have?

Here are some possibilities

23,190  - Actual date value

20,230,629 - Integer that a human might think is in YYYYMMDD style

2,003,651,033 - Actual datetime value

```180  data _null_;
181    today=date();
182    now=datetime();
183    put today=comma20. today=date9. today=yymmddn8. ;
184    put now=comma20. now=datetime19. ;
185  run;

today=23,190 today=29JUN2023 today=20230629
now=2,003,651,033 now=29JUN2023:09:43:53
```
Obsidian | Level 7

## Re: Extract last 5 weeks data

The variable displays value as 'YYYY-MM-DD', i.e., 2023-05-22 and so on. Not sure what format you are asking for.

Super User

## Re: Extract last 5 weeks data

``proc contents data=temp;run;``

Check the actual range of the unformatted values.

``````proc means data=temp min max range ;
var tx_dt;
format tx_dt ;
run;``````
Discussion stats
• 13 replies
• 338 views
• 2 likes
• 3 in conversation