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;
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
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
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;
@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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.