topic Dynamic Where Condition With Date Values in SAS Programming

Dynamic Where Condition With Date Values

random1 — Fri, 14 Oct 2016 02:18:15 GMT

I am trying to subset my dataset based on months, and I want it to loop through the months and create a different dataset for each month.

This is my code:

%macro SASPLay;

%let start = 30JUN2014;

%do i=0 %to 10;

data _null_;

call symputx("mth",put(intnx("MONTHS",input("&start",Date9.),&i,'SAME'),Date9.));

run;

%put &mth;

data final_&mth;

set mockdata;

where DATE = '&mth'd;

run;

%end;

%mend;

%SASPlay;

It gives these error messages:

ERROR: Invalid date/time/datetime constant '&mth'd.

ERROR: Syntax error while parsing WHERE clause.

I am trying to generalise this, because when I use a hardcoded value (e.g. where DATE = '30JUN2014'd; ) it works.

I don't know why I can't just swap out the hardcoded value for &mth, giving that &mth shows as 30JUN2014.

I also tried just using where DATE = &mth; That doesn't work either.

Re: Dynamic Where Condition With Date Values

random1 — Fri, 14 Oct 2016 02:21:23 GMT

Ohh, looks like I got it to work with

where DATE = "&mth"d;

double quotes, not single quotes.

**bleep** this is strange.

Re: Dynamic Where Condition With Date Values

PGStats — Fri, 14 Oct 2016 02:36:47 GMT

macro variable replacement doesn't occur within single quotes. Use double quotes instead

where DATE = "&mth"d;

Re: Dynamic Where Condition With Date Values

ballardw — Fri, 14 Oct 2016 14:31:56 GMT

@random1 wrote:

Ohh, looks like I got it to work with

where DATE = "&mth"d;

double quotes, not single quotes.

**bleep** this is strange.

Not at all strange. The use of double quotes for macro variables to resolve is very well documented. For one thing it allows the use of literal text values containing possible macro invocations such as '%InData' or '&Sons' without the macro processor trying to handle such when you know they are not macro code or macro variables.