BookmarkSubscribeRSS Feed
afiqcjohari
Quartz | Level 8
%macro extract(condition=);
   proc sql;
create table calculatedTable&condition. as select count(*) from table if &condition. is not null then where var = condition; quit; %mend extract; *the following should run proc sql count * with the condition; %extract(condition='a'); *the following should just run a simple sql select count * ; %extract();

The previous calls should give 2 tables in the end

The 'where' statement should only be available if the parametre condition is supplied with a value.

Is it possible to do this?

 

I have a series of SQL statements which have a table as an output.

If there's no condition supplied, the SQL statements will have no 'where' condition.

 

However, if a condition is supplied, the same series of SQL statements will run which will have a table as an ouput.

Naturally macro should be a solution but I can't get my head around macro and proc sql to work.

17 REPLIES 17
gamotte
Rhodochrosite | Level 12

Hello,

 

You have to use the macro language %if.

 

%macro extract(condition=);
   proc sql;
         create table calculatedTable&condition. as
         select count(*) from table
         %if &condition. ne %then %do;
               where var = &condition.;
         %end;
   quit;
%mend extract;
gamotte
Rhodochrosite | Level 12

Note that if you have several calls with no condition, the same output dataset name will be used and will thus be overwritten. You may want to add a suffix parameter to your macro.

 

You also made a mistake in the macro call

%extract(condition='a');

should be

%extract(condition=a);

 

andreas_lds
Jade | Level 19

@gamotte wrote:

Note that if you have several calls with no condition, the same output dataset name will be used and will thus be overwritten. You may want to add a suffix parameter to your macro.

 

You also made a mistake in the macro call

%extract(condition='a');

should be

%extract(condition=a);

 


Problem: the quotes are necessary if var is alphanumeric, so appending a dequoted version of condition to the dataset-name is recommended.

gamotte
Rhodochrosite | Level 12
You're right. I thought "a" was the variable name,
afiqcjohari
Quartz | Level 8
What's the best tips for if the condition here is a date variable.

I want to pass 01JAN2017 as the starting date for example.
Kurt_Bremser
Super User

@afiqcjohari wrote:
What's the best tips for if the condition here is a date variable.

I want to pass 01JAN2017 as the starting date for example.

Depends on how you aquire that date. If you write it literally in your program, you can use the normal method

extract(condition='01jan2017'd)

If you determine it from some other values, you can pass it as the raw numerical value:

%startdate=%sysfunc(today());

%extract(condition=&startdate)

or you set it from a data step:

data _null_;
set infile;
where /* condition */;
call symput('startdate',put(startdate,best.));
run;

 

afiqcjohari
Quartz | Level 8
I keep getting 'Apparent symbolic reference not resolved' if I literally put '01jan2017'd as the date variable.
Kurt_Bremser
Super User

@afiqcjohari wrote:
I keep getting 'Apparent symbolic reference not resolved' if I literally put '01jan2017'd as the date variable.


Please post the log, including the macro definition and the macro call.

afiqcjohari
Quartz | Level 8
I caught it. It's because i'm trying to run the macro in rsubmit. I've written it so that the SAS server can read the macro.
afiqcjohari
Quartz | Level 8
Basically it ends up like this. But it makes me wonder if the rsubmit is a bit redundant..

rsubmit;
%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea where
dTran = &mydate.
;quit;
%mend test;
endrsubmit;
rsubmit;
%test(mydate='01jan2017'd);
endrsubmit;
Kurt_Bremser
Super User

@afiqcjohari wrote:
Basically it ends up like this. But it makes me wonder if the rsubmit is a bit redundant..

rsubmit;
%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea where
dTran = &mydate.
;quit;
%mend test;
endrsubmit;
rsubmit;
%test(mydate='01jan2017'd);
endrsubmit;
rsubmit;

%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea
where dTran = &mydate.
;
quit;
%mend test;

%test(mydate='01jan2017'd);

endrsubmit; 

should also work

afiqcjohari
Quartz | Level 8
calculatedTable&condition. should cover the overwriting issue right? Could you explain what do you mean by suffix parameter here?
gamotte
Rhodochrosite | Level 12

I answered a bit too fast. Since your input dataset is always the same, there is no use to call

the macro twice with no condition. If you had an additional parameter for the input table,

you would have to make sure that two distincts calls give distincts output datasets names,

either by naming the output dataset according to the one given in input, or by adding a custom

suffix when no condition is given

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
  • 17 replies
  • 3604 views
  • 0 likes
  • 5 in conversation