macro syntax error

[jojozheng]

Hi,

I want to assign to first and last date as macro. my code is below but give me syntax error. How can I correct this?

n    date

1 31AUG2018 ---last date

2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017--first date

proc sql noprint;/*assign macro variable as_of_date*/
select catx('', quote(put(date, date9.)), 'd'),catx('', quote(put(date, date9.)), 'd'),n
into :dates1-,
       :as_of_date separated by ','
from z;

%let as_of_date=(&as_of_date);
%let date_last=%qscan(&as_of_date,1);
%let date_first=%qscan(&as_of_date,-1); 

73 proc sql;

73 ! create table Sandbox.hh_new as

74 select ASOFDATE,HHNUM,NEWHH,RETURNHH,STARTDATE

75 from tlake.mkt_hhsumm

76 where ASOFDATE between &date_first and &date_last /*dates1 and dateslast*/

NOTE: Line generated by the macro variable "DATE_FIRST".

76 "30JUN2017"d

_

22

_

76

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

a missing value, (, +, -, BTRIM, CALCULATED, CASE, INPUT, PUT, SUBSTRING, TRANSLATE, USER.

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

77 and HHNUM ne 0

78 and (NEWHH=1 or RETURNHH=1);

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

79 quit;

NOTE: The SAS System stopped processing this step because of errors.

[Kurt_Bremser · Accepted solution]

A simple data step with call symputx will do it:

data z;
input n date :date9.;
format date date9.;
cards;
1 31AUG2018
2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017
;
run;

data _null_;
set z end=eof;
if _n_ = 1 then call symputx('date_last',date);
if eof then call symputx('date_first',date);
run;

The raw values will work in your SQL condition:

proc sql;
create table test as
select * from z
where date between &date_first. and &date_last.;
quit;

[Kurt_Bremser]

PS when posting SAS (or other) logs, always use the {i} button to preserve the formatting.

[PaigeMiller]

Agreeing with @Kurt_Bremser and adding 

There's no need to get human-readable dates into the macro variable. You are trying to do a lot of work to create human readable macro variables, with values like '09AUG18'd, when in fact you only need the actual SAS date value, which is 21405 in this case. This is so much easier programming than formatting the values as you are trying to do.

[Kurt_Bremser]

---

@PaigeMiller wrote:

Agreeing with @Kurt_Bremser and adding 

There's no need to get human-readable dates into the macro variable. You are trying to do a lot of work to create human readable macro variables, with values like '09AUG18'd, when in fact you only need the actual SAS date value, which is 21405 in this case. This is so much easier programming than formatting the values as you are trying to do.

---

Also known as Maxim 28 😉

[novinosrin]

You could skip sequential access with a direct access using point=

data z;
input n date :date9.;
format date date9.;
cards;
1 31AUG2018
2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017
;
run;
data _null_;
do _n_=1,nobs;
set z nobs=nobs point=_n_;
call symputx(ifc(_n_=1,'date_last','date_first'),date);
end;
stop;
run;
%put &date_last   &date_first;