BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
withus
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

20 REPLIES 20
Reeza
Super User

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

withus
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Quartz | Level 8

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
Super User Tom
Super User

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
Fluorite | Level 6

Tom,

Thank YOU so much. It worked beautifully.

DBailey
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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