BookmarkSubscribeRSS Feed
SASlearner97
Obsidian | Level 7

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

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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

SASlearner97
Obsidian | Level 7
i want a dataset like that
Shmuel
Garnet | Level 18

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;
SASlearner97
Obsidian | Level 7
what is rejected
i got :
ERROR: File WORK.REJECTED.DATA does not exist.
Shmuel
Garnet | Level 18

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.

Kurt_Bremser
Super User

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

Kurt_Bremser
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1349 views
  • 0 likes
  • 4 in conversation