topic Re: can't insert date macro into SQL in SAS Programming

can't insert date macro into SQL

withus — Wed, 04 Jun 2014 02:23:31 GMT

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.

Re: can't insert date macro into SQL

Reeza — Wed, 04 Jun 2014 02:31:39 GMT

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

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 13:34:32 GMT

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?

Re: can't insert date macro into SQL

RW9 — Wed, 04 Jun 2014 13:43:02 GMT

You would need to specify its a date:

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

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

Re: can't insert date macro into SQL

RW9 — Wed, 04 Jun 2014 13:47:56 GMT

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;

Re: can't insert date macro into SQL

DBailey — Wed, 04 Jun 2014 13:53:14 GMT

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;

Re: can't insert date macro into SQL

Astounding — Wed, 04 Jun 2014 14:29:14 GMT

Withus,

When Reeza suggested this:

where date between "&year-01-01" and "&year-12-31"

Did you realize that he is using double quotes, and not two single quotes? Did you actually use double quotes and have the program still fail?

Re: can't insert date macro into SQL

Linlin — Wed, 04 Jun 2014 15:02:28 GMT

Reeza is She.

Re: can't insert date macro into SQL

RW9 — Wed, 04 Jun 2014 15:02:36 GMT

Sory to add to your mail Astounding, you would still need to have the letter d after your date literals, e.g. "&year-01-01"d

Otherwise the where is trying to compare numeric date to two characters which wont work

Re: can't insert date macro into SQL

Reeza — Wed, 04 Jun 2014 15:05:12 GMT

The OP states it's a character variable.

My date variable is string.

Re: can't insert date macro into SQL

RW9 — Wed, 04 Jun 2014 15:09:28 GMT

So the where is all wrong then:

from:

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

to (assuming per the other dates that my date is yyyy-mm-dd):

where input(Date,yymmdd10.) between "&year-01-01"d and "&year-12-31"d;

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 15:10:03 GMT

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?

Re: can't insert date macro into SQL

RW9 — Wed, 04 Jun 2014 15:18:01 GMT

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.

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 15:23:09 GMT

I tried, shown below:

"input(DOS, yymmdd10.)" then I got an error message: Invalid date/time "2013-01-01"d

"01Jan&year."d. ....and another error: different data types.

For your convenience I posted fake data and code above.

Re: can't insert date macro into SQL

Reeza — Wed, 04 Jun 2014 15:24:19 GMT

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

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 15:29:46 GMT

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?

Re: can't insert date macro into SQL

Tom — Wed, 04 Jun 2014 15:30:21 GMT

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

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 15:38:39 GMT

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.

Re: can't insert date macro into SQL

jwillis — Wed, 04 Jun 2014 15:42:26 GMT

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;

Re: can't insert date macro into SQL

withus — Wed, 04 Jun 2014 15:45:36 GMT

Tom,

Thank YOU so much. It worked beautifully.