DATA Step, Macro, Functions and more

can't insert date macro into SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

can't insert date macro into SQL

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.


Accepted Solutions
Solution
‎06-04-2014 11:30 AM
Super User
Super User
Posts: 6,502

Re: can't insert date macro into SQL

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(%'))

View solution in original post


All Replies
Super User
Posts: 17,907

Re: can't insert date macro into SQL

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

Occasional Contributor
Posts: 12

Re: can't insert date macro into SQL

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?

Super User
Super User
Posts: 7,413

Re: can't insert date macro into SQL

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;

Super User
Super User
Posts: 7,413

Re: can't insert date macro into SQL

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;

Occasional Contributor
Posts: 12

Re: can't insert date macro into SQL

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?

Super User
Super User
Posts: 7,413

Re: can't insert date macro into SQL

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.

Occasional Contributor
Posts: 12

Re: can't insert date macro into SQL

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?

Super User
Posts: 17,907

Re: can't insert date macro into SQL

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);

Occasional Contributor
Posts: 12

Re: can't insert date macro into SQL

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.

Super User
Super User
Posts: 6,502

Re: can't insert date macro into SQL

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'

Regular Contributor
Posts: 217

Re: can't insert date macro into SQL

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;

Solution
‎06-04-2014 11:30 AM
Super User
Super User
Posts: 6,502

Re: can't insert date macro into SQL

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(%'))

Occasional Contributor
Posts: 12

Re: can't insert date macro into SQL

Tom,

Thank YOU so much. It worked beautifully.

Super Contributor
Posts: 578

Re: can't insert date macro into SQL

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 548 views
  • 1 like
  • 8 in conversation