topic Re: Macro in Statistical Procedures

Macro

Thalitacosta — Tue, 09 Nov 2021 14:57:10 GMT

I need to create in a table the results below for each month. Should I make a macro: Like?

%let dt = ("202006");

proc sql;

create table calc2 as

select DATE,

'00'n,

default,

total,

sum(default) as total_def,

default / sum(default) as dist_def

from calc1

where put(DATE,yymmn6.) = &dt.;

quit;

Re: Macro

sbxkoenk — Tue, 09 Nov 2021 15:10:56 GMT

Hello,

Making a macro that is looping over the months (and then appending all results) seems very sub-optimal to me.

For every macro run / call, you are making another pass through the data.

If you tell us how your input dataset is structured and what you exactly want to achieve, we can help you doing all this in one data step (for all months!).

Koen

Re: Macro

Reeza — Tue, 09 Nov 2021 17:49:14 GMT

 



proc sql;

create table calc2 as

select DATE,
/*add in month for aggregates*/
put(date, yymmn6.) as month,

/*this variable should have a name*/
'00'n as randomVariable,

default,

total,


sum(default) as total_def,

default / sum(default) as dist_def

from calc1
/*group for each month*/
group by calculated month
/*all dates after june 2020*/
where date >= '01Jun2020'd;
;

quit;

Re: Macro

PaigeMiller — Tue, 09 Nov 2021 18:05:20 GMT

@Thalitacosta wrote:

I need to create in a table the results below for each month. Should I make a macro: Like?

%let dt = ("202006");

proc sql;

create table calc2 as

select DATE,

'00'n,

default,

total,

sum(default) as total_def,

default / sum(default) as dist_def

from calc1

where put(DATE,yymmn6.) = &dt.;

quit;

This will never work. When you say %let dt=("202006"); this means that the value of macro variable &DT has parentheses and double quotes and digits. It will never match a DATE variable, because the parentheses and double quotes prevent any matching. It is not the same as if you use %let dt=202006; but even this won't match true date variables without extra work.

As I said in your earlier thread, you have to talk to SAS in the language it understands, and dates are the number of days since 01JAN1960. SAS doesn't understand anything else. So your WHERE statement should be

where DATE between '01JUN2020'd and '31JUN2020'd;

because SAS understands '01JUN2020'd and '31JUN2020'd, it converts '01JUN2020'd to the number of days between 01JAN1960 and 01JUN2020 which is something SAS then can work with. It doesn't understand 202006 without you doing extra work. And it doesn't understand ("202006").

If you need a macro variable DT that changes you can do something like this.

%let dt=202006;
%let month=%sysfunc(inputn(&dt,yymmn6.));

/* Most of your PROC SQL goes here and then */
where intnx('month',date,0,'b')=&month;

Lastly, agreeing with @sbxkoenk , this type of looping that you are asking for is a poor programming practice in most cases, and very inefficient, but we don't really know what you are trying to do.