topic Re: Extract last 5 weeks data in SAS Programming

Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 13:18:31 GMT

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;

Can someone please help me with the code?

Re: Extract last 5 weeks data

PaigeMiller — Thu, 29 Jun 2023 13:26:01 GMT

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.

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 13:28:37 GMT

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?

Re: Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 13:38:18 GMT

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?

Re: Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 13:45:37 GMT

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?

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 13:47:20 GMT

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

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 13:48:50 GMT

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.

Re: Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 13:59:04 GMT

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

Re: Extract last 5 weeks data

PaigeMiller — Thu, 29 Jun 2023 14:00:00 GMT

@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.

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 14:00:12 GMT

Check the variable's metadata (type,length,format,label,etc).

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;

Re: Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 14:01:50 GMT

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

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 14:08:16 GMT

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;

Re: Extract last 5 weeks data

hk24 — Thu, 29 Jun 2023 14:15:10 GMT

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?

Re: Extract last 5 weeks data

Tom — Thu, 29 Jun 2023 14:21:17 GMT

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