topic Re: Find two weeks interval's data in New SAS User

Find two weeks interval's data

SASlearner97 — Fri, 11 Dec 2020 08:10:43 GMT

Hello everyone,

I have an Excel file 'have' with data in different dates

and i want to make a table of the current days of the week and select every observation from 'have' and put it in the corresponding day of the current week

I joined an Excel file with two papers , the first one shows the table 'have' and the second paper shows the paper 'want' ,

Thanks for helping me

Re: Find two weeks interval's data

PeterClemmensen — Fri, 11 Dec 2020 08:21:03 GMT

Is this for reporting purposes or do you want a SAS dataset like this?

Re: Find two weeks interval's data

SASlearner97 — Fri, 11 Dec 2020 08:28:01 GMT

i want a dataset like that

Re: Find two weeks interval's data

Shmuel — Fri, 11 Dec 2020 09:57:34 GMT

I assume you know how to import the excel file into have;

Check next code. The want table cannot be as shown - you cannot have two variables with the same name (the week day) nor imbedded spaces in their names.

proc sort data=have; by date; run;

data temp;
 set have rejected;
     start_cur_week = today() - weekday(today()) +1;
	 end_cur_week = start_cur_week +7;
	 end_nxt_week = start_cur_week +14;
	 if start_cur_week le date lt end_cur_week then week=1; else
	 if end_cur_week le date lt end_nxt_week then week=2; 
	 else week=0;
	 
	 if week=0 or weekday(date) in (1,7) /* Sunday, Saturday */
	    then do; output rejected; return; end;
	 
	 array var {*} var1-var14;
	 i = date - start_cur_week +1;
	 var(i) = date;	
	 keep title week date var2-var6 var9-var13;
run;

Re: Find two weeks interval's data

SASlearner97 — Fri, 11 Dec 2020 10:16:31 GMT

what is rejected
i got :
ERROR: File WORK.REJECTED.DATA does not exist.

Re: Find two weeks interval's data

Shmuel — Fri, 11 Dec 2020 11:08:38 GMT

Sorry, my fault. Code should be:

proc sort data=have; by date; run;

data temp rejected;
 set have;
     start_cur_week = today() - weekday(today()) +1;
	 end_cur_week = start_cur_week +7;
	 end_nxt_week = start_cur_week +14;
	 if start_cur_week le date lt end_cur_week then week=1; else
	 if end_cur_week le date lt end_nxt_week then week=2; 
	 else week=0;
	 
	 if week=0 or weekday(date) in (1,7) /* Sunday, Saturday */
	    then do; output rejected; return; end;
	 
	 array var {*} var1-var14;
	 i = date - start_cur_week +1;
	 var(i) = date;	
	 keep title week date var2-var6 var9-var13;
run;

In any case a date is out of the range, a previous week or after the next week or falling on Saturday or Sunday - data will be rejected for review.

Re: Find two weeks interval's data

Kurt_Bremser — Fri, 11 Dec 2020 11:23:15 GMT

@SASlearner97 wrote:
i want a dataset like that

I say you don't want such a dataset; a dataset like this is impossible to do further work with. I rather think you need a report that you put into another spreadsheet so that someone can have a look at it.

Anyway, here's your original spreadsheet in SAS dataset form:

data have;
input title $ case sending_date :mmddyy10.;
format sending_date yymmdd10.;
datalines;
a 0 12/11/2020
b 0 11/8/2020
c 1 12/8/2020
d 1 12/18/2020
e 1 12/11/2020
f 1 11/18/2020
g 0 12/14/2020
;

Check if that represents your data correctly; spreadsheets are not suited to represent datasets, as they do not convey important metadata (column types, lengths, formats). On top of that, many corporate firewalls block their download for security reasons, so you automatically prevent a lot of the people here from helping you (this post has been done from my private PC).

According to your rules, title f should not appear in your result, as it has a date from a previous week.

Re: Find two weeks interval's data

Kurt_Bremser — Fri, 11 Dec 2020 11:46:25 GMT

Here's code that creates a report in your wanted format:

%let repdate=%sysfunc(inputn(2020-12-08,yymmdd10.));
%let curweek=%sysfunc(week(&repdate.));
%let nweek=%eval(&curweek. + 1);

proc format;
value wday
  2 = "Monday"
  3 = "Tuesday"
  4 = "Wednesday"
  5 = "Thursday"
  6 = "Friday"
;
value weeklabel
  &curweek. = "Current Week"
  &nweek. = "Next Week"
;
run;

data pre;
set have;
where intnx('week',&repdate,0,'b') le sending_date le intnx('week',&repdate.,1,'e');
week = week(sending_date);
weekday = weekday(sending_date);
run;

proc report data=pre;
column ("Title" title) (week,(weekday,sending_date)) n;
define title / "" group;
define week / "" across format=weeklabel.;
define weekday / "" across format=wday. preloadfmt order=data;
define sending_date / "" display;
define n / noprint;
run;

If you wrap that into ODS EXCEL, you get your spreadsheet.


	Current Week					Next Week
Title	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
a	.	.	.	.	2020-12-11	.	.	.	.	.
c	.	2020-12-08	.	.	.	.	.	.	.	.
d	.	.	.	.	.	.	.	.	.	2020-12-18
e	.	.	.	.	2020-12-11	.	.	.	.	.
g	.	.	.	.	.	2020-12-14	.	.	.	.