topic Re: Getting past weeks data in New SAS User

Getting past weeks data

Vasundha — Thu, 17 Nov 2022 12:07:34 GMT

Hi All,

I need a small help from you all. I've a table with a date variable in that I wanna get past week's data. I used a condition called where=(date= today() -7) but the thing here is for that date variable it has no weekends and Mondays in it. Please help me out to get the past 7 days data irrespective of date values in it. Thanks in advance.

Re: Getting past weeks data

PeterClemmensen — Thu, 17 Nov 2022 12:21:37 GMT

You say you want the past weeks data. But really, you want data from the latest 7 dates in your data, correct?

Re: Getting past weeks data

Vasundha — Thu, 17 Nov 2022 12:26:15 GMT

Correct!

Re: Getting past weeks data

PeterClemmensen — Thu, 17 Nov 2022 12:37:55 GMT

Here's an approach. See if you can use that as a template.

proc rank data = sashelp.stocks out = rank ties = dense descending;
   var date;
   ranks r;
run;

data want;
   set rank;
   where r <= 7;
run;

Re: Getting past weeks data

Vasundha — Thu, 17 Nov 2022 12:40:13 GMT

Appreciate it. Can't it be possible to keep a condition in the input table in a data step?.

Data want;

Set have (condition);

Somewhat like that?.

Re: Getting past weeks data

PeterClemmensen — Thu, 17 Nov 2022 12:54:47 GMT

No. Not directly. You would have to do some pre-processing of your data. Otherwise, how would you know the 7'th largest date in your data?

Re: Getting past weeks data

Vasundha — Thu, 17 Nov 2022 12:58:46 GMT

7th largest?. What I meant was I require a condition to get the past 7 days data irrespective of dates in my table. And FYI, they don't have weekends and Mondays .

Re: Getting past weeks data

PeterClemmensen — Thu, 17 Nov 2022 13:09:23 GMT

Same thing. SAS Dates are integers. If you want to retrieve data for the latest 7 days of your data, you would have to know the 7'th largest value of date and retrieve everything above that.

Re: Getting past weeks data

ballardw — Thu, 17 Nov 2022 15:22:51 GMT

@Vasundha wrote:

Hi All,

I need a small help from you all. I've a table with a date variable in that I wanna get past week's data. I used a condition called where=(date= today() -7) but the thing here is for that date variable it has no weekends and Mondays in it. Please help me out to get the past 7 days data irrespective of date values in it. Thanks in advance.

That code wouldn't work because you are using =, so it only finds one date.

Did you try

 where=( date ge ( today() -7 ) )

Re: Getting past weeks data

Vasundha — Fri, 18 Nov 2022 07:56:01 GMT

Thanks for replying me, I've tried your code it gives me just 3 dates cuz when we're using today () -7 function in that condition it is counting weekends, Mondays as well. That's why I got only 3 dates as output.

Explanation : today () -7

17Nov22

16Nov22

15Nov22.

Alongside these 3 dates I must also get 11Nov22, 10Nov22, 09Nov22 & 08Nov22.

Note: like I mentioned earlier I've no weekends and Mondays in my data for some reason.

Re: Getting past weeks data

PeterClemmensen — Fri, 18 Nov 2022 07:54:30 GMT

@Vasundha , did you try the approach that I posted above? If it does not suit you and you have not yet found your answer, I encourage you to post some sample data for us to work with that resembles your actual data and your desired result. Makes it much easier to provide a usable code answer.

Re: Getting past weeks data

Kurt_Bremser — Fri, 18 Nov 2022 07:56:38 GMT

Brute force approach:

proc sort
  data=have (keep=date)
  out=dates
  nodupkey
;
by descending date;
run;

data _null_;
set dates point=7;
call symputx("cutoff",date);
stop;
run;

data want;
set have;
where date ge &cutoff.;
run;

But the PROC RANK method is more efficient.

Re: Getting past weeks data

Vasundha — Fri, 18 Nov 2022 08:04:43 GMT

Sure, here's my data.

Data have;
Input in_date : date9. readings;
format in_date date9.;
Datalines;
18NOV2022 10
17NOV2022 12
16NOV2022 15
15NOV2022 45
11NOV2022 67
10NOV2022 60
09NOV2022 61
08Nov2022 89
04Nov2022 40
;
run;
Note: I require a condition in the input table to get the  past 7 days data i.e

@PeterClemmensen wrote:
@Vasundha , did you try the approach that I posted above? If it does not suit you and you have not yet found your answer, I encourage you to post some sample data for us to work with that resembles your actual data and your desired result. Makes it much easier to provide a usable code answer.

A condition like this : 
Data want;
Set have(condition);
run;

 


18Nov2022 10
17Nov2022 12
16Nov2022 15
15Nov2022 45
11Nov2022 67
10Nov2022 60
09Nov2022 61

Re: Getting past weeks data

PeterClemmensen — Fri, 18 Nov 2022 08:07:51 GMT

Is this data representative? Do you have only 1 obs per date? Is the data sorted descending by date?

Re: Getting past weeks data

Vasundha — Fri, 18 Nov 2022 08:19:40 GMT

Yes, I've only one obs per date and they're in ascending order in my table. My bad I gave you data in descending order. Please Nevermind.

@PeterClemmensen wrote:
Is this data representative? Do you have only 1 obs per date? Is the data sorted descending by date?

Re: Getting past weeks data

PeterClemmensen — Fri, 18 Nov 2022 08:31:09 GMT

So really, you want to get the last 7 obs of your data, correct?

Re: Getting past weeks data

PeterClemmensen — Fri, 18 Nov 2022 08:33:23 GMT

If so, then try this

Data have;
Input in_date : date9. readings;
format in_date date9.;
Datalines;
04Nov2022 40
08Nov2022 89
09NOV2022 61
10NOV2022 60
11NOV2022 67
15NOV2022 45
16NOV2022 15
17NOV2022 12
18NOV2022 10
;
run;

data want;
  do p = max(1, n - 6) to n;
    set have nobs = n point = p;
    output;
  end;
  stop;
run;

Re: Getting past weeks data

Vasundha — Fri, 18 Nov 2022 08:40:30 GMT

Right

Re: Getting past weeks data

Vasundha — Fri, 18 Nov 2022 08:42:42 GMT

I really appreciate your efforts however I was wondering that can't it be possible in input table in a data step?.

Re: Getting past weeks data

Kurt_Bremser — Fri, 18 Nov 2022 08:50:32 GMT

To evaluate data with SAS, it must first be brought into SAS. First import, then use one of the methods suggested, then subset.