can't insert date macro into SQL

[withus]

Greetings!

I am a beginner to Macro.

My date variable is string.

My original code is shown below:

%macro plan(year);

PROC SQL;

create table pop_&year  as

select *

from b inner join a

on a.ID=b.ID

where Date between '&year-01-01' and '&year-12-31';

quit;

%mend;

%plan(2013);

It's not working. I tried ""&year"||"-01-01"", but it didn't work, either.

Please, help me out.

I need to create 10 datasets:

pop_2013:  Date between '2013-01-01' and '2013-12-31'

pop_2012:  Date between '2012-01-01' and '2012-12-31'

pop_2011:  Date between '2011-01-01' and '2011-12-31'

and so on.

Thanks in advance.

[Reeza]

Macro variables only resolve in double quotes, not single quotes.  "&year-01-01" which would be a character variable

[withus]

When I tried "&year-01-01", I got an error message, "Invalid column name"  '&year-01-01'.

I tried below, too, but I got different error messages:

""&year-01-01"" : An object or column name is missing

"'&year-01-01'"   : Invalid column name   ' '&year-01-01' '

' "&year-01-01" ' : Conversion failed when converting date or time from character

Any suggestions?

[RW9]

You would need to specify its a date:

where Date between '&year-01-01' and '&year-12-31';

To

where Date between "&year-01-01"d and "&year-12-31"d;

[RW9]

To note, you could also achieve the end result by (not-tested):

data _null_;

  do i=2001 to 2013;

    year=put(i,4.);

    call execute('  proc sql;

                      create table POP_'||year||' as

                      select  *

                      from    B

                      inner join A

                      on      A.ID=B.ID

                      where   date between '||input(year||'-01-01',yymmdd10.)||' and '||input(year||'-12-31',yymmdd10.)||';

                    quit');

  end;

run;

[withus]

Hi RW9,

I got an error.

without data it may be hard to test the code.

Here are fake data.

data family;

infile datalines;

input @1 ID $1. @3 DOS $11. @15 day 2.;

datalines;

A 2011-01-05 11

A 2012-01-05 10

A 2013-01-05 12

B 2013-12-25 13

B 2012-12-25 12

B 2011-12-25 11

C 2011-11-15 9

C 2012-11-15 10

C 2013-11-15 11

;

data school;

infile datalines;

input @1 ID $1. @3 edu 2.;

datalines;

A 5

B 12

C 19

;

Without macro or do loop, this worked.

proc sql;

create table pop_2013 as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID

where DOS between '2013-01-01' and '2013-12-31';

quit;

I tried your do loop, but I got an error message.

data _null_;

do i=2011 to 2013;

year=put(i, 4.);

call execute('proc sql;

create table pop_'||year||' as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID

where DOS between '||input(year||'-01-01', yymmdd10.) and '||input(year||'-12-31', yymmdd10.)||';

quit');

end;

run;

I tried macro, but I got an error message, too.

%macro year(year);

proc sql;

create table pop_&year as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID

where DOS between "&year-01-01" and "&year-12-31";

quit;

It didn't work. I tried more using macro so far:

"&year-01-01"

""&year-01-01""

" '&year-01-01' "

' "&year-01-01" '

' "&year||"-01-01" '

Any advice?

[RW9]

Well, there are some typos in your code for the data _null_ suggestion, here it is updated:

data _null_;
  do i=2011 to 2013;
  year=put(i, 4.);
  call execute('proc sql;
                  create table pop_'||year||' as
                  select a.*, b.edu
                  from family a inner join school b
                  on a.ID=b.ID
                  where input(DOS,yymmdd10.) between '||input(year||'-01-01', yymmdd10.)||' and '||input(year||'-12-31', yymmdd10.)||';
                  quit;');
  end;
run;

Your macro isn't working as the DOS is still character and needs to be input as YYMMDD10., also the other dates need d after them to make them date literals.  Please see my other post for the where clause update.

[withus]

Hi RW9,

I still got an error.

Here is a log.

NOTE: CALL EXECUTE generate line.

call execute('proc sql;

                  create table pop_'||year||' asselect a.*, b.edu

Syntax error, expecting one of the follow: (, '.', AS, LIKE

on the code, there is space between "as" and "select".

Thus, I changed line between them, but I still got an error.

Any idea?

[Reeza]

You don't have to do the input conversion, though perhaps you should.

data family;

infile datalines;

input @1 ID $1. @3 DOS $11. @15 day 2.;

datalines;

A 2011-01-05 11

A 2012-01-05 10

A 2013-01-05 12

B 2013-12-25 13

B 2012-12-25 12

B 2011-12-25 11

C 2011-11-15 9

C 2012-11-15 10

C 2013-11-15 11

;

data school;

infile datalines;

input @1 ID $1. @3 edu 2.;

datalines;

A 5

B 12

C 19

;

%macro by_year(year);

proc sql;

create table pop_&year as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID

where DOS between "&year.-01-01" and "&year.-12-31";

quit;

%mend;

%by_year(2013);

%by_year(2012);

%by_year(2011);

[withus]

Here is the log. Not working.

proc sql;

create table pop_2013 as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID 870  where DOS  between '2013-01-01' and '2013-12-31';

NOTE: Table WORK.POP_2013 created, with 5 rows and 4 columns.

quit;

%macro year(year);

proc sql;

create table pop_&year as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID 878  where DOS  between '&year.-01-01' and '&year.-12-31';

quit;

%mend; 881  %year(2013);

NOTE: Table WORK.POP_2013 created, with 0 rows and 4 columns.

[Tom]

Macro expressions are NOT evaluated inside of single quotes.

You need to use the double quote character instead of the single quote character to allow the value of &YEAR to be expanded.

use:

"&year.-01-01"

instead of

'&year.-01-01'

[jwillis]

Dear withus,

The SQL macro suggestion above is working for me.

292

293 %yeary(yeary=2013);

MPRINT(YEARY): proc sql;

MPRINT(YEARY): create table pop_2013 as select a.*, b.edu from family a inner join school b on a.ID=b.ID where DOS

between "2013-01-01" and "2013-12-31";

NOTE: Table WORK.POP_2013 created, with 3 rows and 4 columns.

MPRINT(YEARY): quit;

NOTE: PROCEDURE SQL used (Total process time):

"yeary = 2013"

This code worked all the way through for me.

options macrogen mprint;
%global chkit;
%macro yearz(year);
proc sql;
create table pop_&year. as

select a.*, b.edu

from family a inner join school b

on a.ID=b.ID

where DOS between "&year.-01-01" and "&year.-12-31";

quit;
%put "year = &year.";
%put "chkit = &chkit.";
%mend yearz;

%let chkit=2013;
%yearz(&chkit.);

proc print data=work.pop_&chkit.;
run;

[Tom · Accepted solution]

It looks from your examples like it seems to make a difference whether you includes the strings that look like dates in single or double quotes.

So it sounds like you are not using the code your originally posted but are instead using explicit passthrough to submit SQL code to a remote database that interprets strings in double quotes as variable or table names and strings in single quotes as literals.

To generate strings in single quotes you could use something like this:

where Date between %unquote(%str(%')&year-01-01%str(%')) and %unquote(%str(%')&year-12-31%str(%'))

[withus]

Tom,

Thank YOU so much. It worked beautifully.

[DBailey]

presuming date is actually a date...

create table pop_&year  as

select *

from b inner join a

on a.ID=b.ID

where Date between "01Jan&year."d and "31Dec&year."d;

quit;