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

Hi all,

I am trying to create a macro that creates different tables by using date variable in filter. And it takes date variables from another table. If date is missing then it doesn't create a table, if date is not  missing then it uses variable in filter and create a table.

I tried to write a code but it didn't work. Thanks in advance for your help.

 

here is the code;

data check;
input data $5. date DATETIME20. ;
FORMAT DATE DATETIME20. ;

DATALINES;
Data1
Data2 11AUG2020:20:15:20
;
RUN;

 

data sample;
input id $1. date datetime20. ;
FORMAT DATE DATETIME20. ;

DATALINES;
x 01sep2020:19:17:20
y 11AUG2020:20:15:20
t 01sep2020:19:17:20
v 11AUG2020:20:15:20
a 01sep2020:19:17:20
b 11AUG2020:20:15:20
c 01sep2020:19:17:20
d 11AUG2020:20:15:20
;
RUN;

 


%macro create_data(Data,date);

 

 %if '&date.'dt =. %then %do;

%abort ;

%end;

%else %do;

data &data. ;

set sample (where=(date>='&date.'dt));

run;

%end;

%mend;


data _NULL_ ;
SET check;
CALL EXECUTE("%create_data("||data||","||date||");");
RUN;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

The way you are generating the code to push to CALL EXECUTE() will put the raw number of seconds from your DATETIME value into the macro variable.  That will actually make your macro easier to write since you won't have to deal with datetime formatted strings.

%macro create_data(Data,date);
%if (&date=.) or (&date=) %then %put NOTE: &=data not processed. ;
%else %do;
  %put NOTE: Creating &data for dates on or after &date [%sysfunc(putn(&date,datetime20.))]. ;
data &data. ;
  set sample (where=(date>=&date));
run;
%end;
%mend;

options mprint;
data _null_ ;
  SET check;
  CALL EXECUTE(cats('%nrstr(%create_data)(',data,',',date,')'));
run;
342   options mprint;
343   data _null_ ;
344     SET check;
345     CALL EXECUTE(cats('%nrstr(%create_data)(',data,',',date,')'));
346   run;

NOTE: There were 2 observations read from the data set WORK.CHECK.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


NOTE: CALL EXECUTE generated line.
1    + %create_data(Data1,.)
NOTE: DATA=Data1 not processed.
2    + %create_data(Data2,1912796120)
NOTE: Creating Data2 for dates on or after 1912796120 [  11AUG2020:20:15:20].
MPRINT(CREATE_DATA):   data Data2 ;
MPRINT(CREATE_DATA):   set sample (where=(date>=1912796120));
MPRINT(CREATE_DATA):   run;

NOTE: There were 8 observations read from the data set WORK.SAMPLE.
      WHERE date>=1912796120;
NOTE: The data set WORK.DATA2 has 8 observations and 2 variables.

 

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

I think there are many errors here, the first one being

 

 %if '&date.'dt =. %then %do;

because the value of macro variable &DATE does not get resolved inside of single quotes; it must be inside double-quotes. And I see a bunch of other errors as well.

 

IMPORTANT TIP FOR WRITING MACROS: create code that works without macros and without macro variables first, for two values of DATE. You haven't done that. If you can't get code without macros and without macro variables to work, then you won't be able to get code with macros and with macro variables to work. So, please show us code that works without macros and without macro variables for two values of DATE.

 

But I don't think macros are needed here, depending on what you want to do after you create these data sets. What are you going to do with these data sets once you create them?

 

--
Paige Miller
Tom
Super User Tom
Super User

The way you are generating the code to push to CALL EXECUTE() will put the raw number of seconds from your DATETIME value into the macro variable.  That will actually make your macro easier to write since you won't have to deal with datetime formatted strings.

%macro create_data(Data,date);
%if (&date=.) or (&date=) %then %put NOTE: &=data not processed. ;
%else %do;
  %put NOTE: Creating &data for dates on or after &date [%sysfunc(putn(&date,datetime20.))]. ;
data &data. ;
  set sample (where=(date>=&date));
run;
%end;
%mend;

options mprint;
data _null_ ;
  SET check;
  CALL EXECUTE(cats('%nrstr(%create_data)(',data,',',date,')'));
run;
342   options mprint;
343   data _null_ ;
344     SET check;
345     CALL EXECUTE(cats('%nrstr(%create_data)(',data,',',date,')'));
346   run;

NOTE: There were 2 observations read from the data set WORK.CHECK.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


NOTE: CALL EXECUTE generated line.
1    + %create_data(Data1,.)
NOTE: DATA=Data1 not processed.
2    + %create_data(Data2,1912796120)
NOTE: Creating Data2 for dates on or after 1912796120 [  11AUG2020:20:15:20].
MPRINT(CREATE_DATA):   data Data2 ;
MPRINT(CREATE_DATA):   set sample (where=(date>=1912796120));
MPRINT(CREATE_DATA):   run;

NOTE: There were 8 observations read from the data set WORK.SAMPLE.
      WHERE date>=1912796120;
NOTE: The data set WORK.DATA2 has 8 observations and 2 variables.

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 319 views
  • 1 like
  • 3 in conversation