Creating a subsample based on after date condition

[Phil_NZ]

Hi all SAS Users,

I have a dataset romania as below

GVKEY	IID	DATADATE
212834	01W	03JAN2002
212834	01W	04JAN2002
212834	01W	07JAN2002
212834	01W	08JAN2002
212834	01W	09JAN2002
212834	01W	14JAN2002
212834	01W	15JAN2002

What I want is I want to get the observation after 08JAN2002, so, my desired output is

GVKEY    IID   DATADATE
212834	01W	   09JAN2002
212834	01W	   14JAN2002
212834	01W	   15JAN2002

I tried to code but it does not work properly

DATA SAMPLE3;
	SET romania (where=(datadate > 08JAN2002 ));
run;

The log is

35         	SET romania (where=(datadate > 08JAN2002 ));
                                             _______
                                             22
                                             76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT, 
              NE, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

Could you please help me to sort it out?

Warm regards.

[qoit · Accepted solution]

SET romania (where=(datadate > "08JAN2002"d ));

will work.

[Phil_NZ]

Hi @qoit 

Thank you for your solution, I am of curiosity, For example I want to get the monthly data after this day

I replicate your code but it does not work, could you please help me to sort it out?

DATA SAMPLE3;
	SET romania (where=(datadate > "01JUL2005"m )); /*I changed from d to m here*/
run;

proc means data=SAMPLE3;
class curcdd;
var exchg;
run;

The log is

35         	SET romania (where=(datadate > "01JUL2005"m ));
                                           ____________
                                           49         22
                                                      76
ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT, 
              NE, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

Warm regards.

[qoit]

Yes, it wouldn't work. You need to affix a D (not any other alphabet). D is a date constant which tells SAS to convert the value to a SAS date at the compile time.

[ballardw]

---

@Phil_NZ wrote:

Hi @qoit 

Thank you for your solution, I am of curiosity, For example I want to get the monthly data after this day

I replicate your code but it does not work, could you please help me to sort it out?

DATA SAMPLE3;
	SET romania (where=(datadate > "01JUL2005"m )); /*I changed from d to m here*/
run;

proc means data=SAMPLE3;
class curcdd;
var exchg;
run;

The log is

35         	SET romania (where=(datadate > "01JUL2005"m ));
                                           ____________
                                           49         22
                                                      76
ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT, 
              NE, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

Warm regards.

---

I am not sure what you mean by "get the monthly data after this day" but one interpretation for the attempted code could be "create a summary for each calendar month" which can be done by including the date variable as a class variable and using a format with the date variable that creates "month" intervals. Note, there is no actual need for a separate data set as you can use a Where a statement in proc mean (or summary or almost any procedure).

proc means data=romania;
   where datadate > "01JUL2005"d;
   class curcdd datadate;
   format datadate mmyys7.; /* mm/yyyy appearance 07/2005*/
   /* or mmyyp7  07.2005, mmyyd7. 07-2005 mmyyc7. 07:2005
     yymmc7. 2005:07 (and other versions of yymmx
     yymm7. 2005M07  monyy7. JUL2005 
   */
   var exchg;
run;

Groups created by applying a format to variable like this will be honored by almost all the report, analysis and graphing procedures.

For dates, times and datetime values you can create a lot of custom formats as well using Proc Format.

[Phil_NZ]

@ballardw 

An amazing suggestion and explanation for me, which really benefits me in the future because I am keen on something that I can play with like that.

Warmest rergards.