update
given new approach using intnx think can use loop simplify things more. if made array:
data; array period [4] $ var1-var4 ('day' 'week' 'month' 'year'); run; and tried make loop each element:
%macro sqlloop; proc sql; %do k = 1 %to dim(period); /* in case decide drop array later */ %let bucket = &period(k) create table output.t_&bucket ( select intx( "&bucket.", date_field, o, 'e') test table); %end quit; %mend %sqlloop this doesn't quite work, captures idea want. run query each of values in intx. make sense?
i have couple of prior questions i'm merging one. got helpful advice on others , can tie together.
i have following function creates dynamic string populate select statement in sas proc sql; code block:
proc fcmp outlib = output.funcs.test; function sqlselectbydaterange(interval $, date_field $) $; day = date_field||" day, "; week = "week("||date_field||") week, "; month = "month("||date_field||") month, "; year = "year("||date_field||") year, "; if interval = "week" do; day = ''; end; if interval = "month" do; day = ''; week = ''; end; if interval = "year" do; day = ''; week = ''; month = ''; end; where_string = day||week||month||year; return(where_string); endsub; quit; i've verified creates kind of string want:
data _null_; q = sqlselectbydaterange('month', 'mydatecolumn'); put q =; run; this yields:
q=month(mydatecolumn) month, year(mydatecolumn) year, this want sql string be. prior questions, believe need call function in macro. want this:
%macro sqlselectbydaterange(interval, date_field); /* code can't figure out */ %mend proc sql; create table output.t ( select %sqlselectbydaterange('month', 'mydatecolumn') output.mytable ); quit; i having trouble understanding how make code call macro , interpret part of sql select string. i've tried of previous examples in other answers can't make work. i'm hoping more specific question can me fill in missing step can learn how in future.
two things:
first, should able use %sysfunc call custom function.
%macro sqlselectbydaterange(interval, date_field); %sysfunc( sqlselectbydaterange(&interval., &date_field.) ) %mend; note should not use quotation marks when calling function via sysfunc. also, you cannot use sysfunc fcmp functions until sas 9.2. if using earlier version, not work.
second, have trailing comma in select clause. may need dummy column in following:
proc sql; create table output.t ( select %sqlselectbydaterange('month', 'mydatecolumn') 0 dummy output.mytable ); quit; (notice there no comma before dummy, comma embedded in macro.)
update
i read comment on answer:
i need able different date ranges , on ad-hoc basis, it's want "by month june december" or "weekly 2 years" etc when makes request.
i think can recommend easier way accopmlish doing. first, i'll create simple dataset dates , values. dates spread throughout different days, weeks, months , years:
data work.accounts; format opened yymmdd10. value dollar14.2 ; input opened yymmdd10. value dollar14.2 ; datalines; 2012-12-31 $90,000.00 2013-01-01 $100,000.00 2013-01-02 $200,000.00 2013-01-03 $150,000.00 2013-01-15 $250,000.00 2013-02-10 $120,000.00 2013-02-14 $230,000.00 2013-03-01 $900,000.00 run; you can use intnx function create third column round "opened" column time period, such 'week', 'month', or 'year' (see complete list):
%let period = year; proc sql noprint; create table work.periodsummary select intnx( "&period.", opened, 0, 'e' ) period_end format=yymmdd10. , sum( value ) totalvalue format=dollar14. work.accounts group period_end ; quit; output week:
period_end totalvalue 2013-01-05 $540,000 2013-01-19 $250,000 2013-02-16 $350,000 2013-03-02 $900,000 output month:
period_end totalvalue 2012-12-31 $90,000 2013-01-31 $700,000 2013-02-28 $350,000 2013-03-31 $900,000 output year:
period_end totalvalue 2012-12-31 $90,000 2013-12-31 $1,950,000 
Comments
Post a Comment