Prev: sas problem.
Next: Generating unique string identifier
From: Dean on 26 Jul 2010 04:24
I'm trying to replicate a macro PROC SQL method below (SAS Global
Forum, 101-2010) to summarize regional sales data on a quarter and
year to date basis, however I don't think the method below to
calculate the quarter to date (QTD) sales is correct. You may verify
this by putting a future number, i.e. %let today=
%sysfunc(mdy(1,1,2011)), and it still reports a QTD figure despite no
sales for the period. Can anyone verify the error/spot the mistake in
the code, or suggest a different method for QTD? A similar method for
YTD would be helpful too ;)


/* Create testing data */
data sales;
dt0=mdy(1,1,2010);
do id=101 to 300;
date=dt0+ceil(ranuni(101)*120);
n=ceil(ranuni(101)*3);
if mod(n,3)=1 then region='01';
else if mod(n,3)=2 then region='02';
else region='03';
amount=ceil(ranuni(101)*30)*100;;
output;
end;
format date mmddyy10.;
keep id date region amount;
run;

/* Assume the program is run at 1st day of the month */
%let today=%sysfunc(today());
%let curr_month=%sysfunc(intnx(month,&today,-1),yymmn6.);
%let prev_month=%sysfunc(intnx(month,&today,-2),yymmn6.);
%let qtr=
%sysfunc(ceil(%sysfunc(month(%sysfunc(intnx(month,&today,-1))))/3));
proc sql;
select region,
sum((put(date,yymmn6.)="&curr_month")*amount) as Curr,
sum((put(date,yymmn6.)="&prev_month")*amount) as Prev,
sum((qtr(date)=&qtr)*amount) as QTD,
sum(amount) as YTD
from sales
group by region;
quit;
From: Patrick on 26 Jul 2010 10:16
Doing exactly what you proposed and using
%let today= %sysfunc(mdy(1,1,2011));
right AFTER the existing %let today=... statement in the code you've
posted, and QTD get's '0'.

The only "issue" I can see with this SQL is that YTD actually just
sums up whatever is in the data set - for example 2 years if there are
2 years in it.
You would have to apply a similar logic for YTD as done for the other
summed vars or you have to subset the data with a where clause so that
it only includes the year you want to report on.

HTH
Patrick
From: Dean on 27 Jul 2010 06:46
Thank you for your reply Patrick. I can't replicate the error now - I
must have made an error myself. My problem when using real data is
that the QTR function in SAS seems to use United States fiscal years,
not the local region's fiscal year.

It's not clear to me what the following code is doing other than that
it is feeding into the PROC SQL procedure and uses the built in QTR
function (confusingly they use the same qtr name) - how can you make a
similar macro function so for any given day you can run a report to
pull quarter to date sales (where the fiscal quarter starts 1 July)?

%let qtr=
%sysfunc(ceil(%sysfunc(month(%sysfunc(intnx(month,&today,-1))))/3));




On Jul 27, 12:16 am, Patrick <patrick.mat...(a)gmx.ch> wrote:
> Doing exactly what you proposed and using
> %let today= %sysfunc(mdy(1,1,2011));
> right AFTER the existing %let today=... statement in the code you've
> posted, and QTD get's '0'.
>
> The only "issue" I can see with this SQL is that YTD actually just
> sums up whatever is in the data set - for example 2 years if there are
> 2 years in it.
> You would have to apply a similar logic for YTD as done for the other
> summed vars or you have to subset the data with a where clause so that
> it only includes the year you want to report on.
>
> HTH
> Patrick

From: Dean on 27 Jul 2010 06:50
*for clarity I meant to say: "so for any given day you can run a
report to sum quarter to date sales"



On Jul 27, 8:46 pm, Dean <dean.par...(a)gmail.com> wrote:
> Thank you for your reply Patrick. I can't replicate the error now - I
> must have made an error myself. My problem when using real data is
> that the QTR function in SAS seems to use United States fiscal years,
> not the local region's fiscal year.
>
> It's not clear to me what the following code is doing other than that
> it is feeding into the PROC SQL procedure and uses the built in QTR
> function (confusingly they use the same qtr name) - how can you make a
> similar macro function so for any given day you can run a report to
> pull quarter to date sales (where the fiscal quarter starts 1 July)?
>
> %let qtr=
> %sysfunc(ceil(%sysfunc(month(%sysfunc(intnx(month,&today,-1))))/3));
>
> On Jul 27, 12:16 am, Patrick <patrick.mat...(a)gmx.ch> wrote:
>
>
>
> > Doing exactly what you proposed and using
> > %let today= %sysfunc(mdy(1,1,2011));
> > right AFTER the existing %let today=... statement in the code you've
> > posted, and QTD get's '0'.
>
> > The only "issue" I can see with this SQL is that YTD actually just
> > sums up whatever is in the data set - for example 2 years if there are
> > 2 years in it.
> > You would have to apply a similar logic for YTD as done for the other
> > summed vars or you have to subset the data with a where clause so that
> > it only includes the year you want to report on.
>
> > HTH
> > Patrick

From: Dean on 28 Jul 2010 08:12
I found a method posted 16 years ago to this group that does what I
want (I don't require QTD but YTD - but the method described is so
brilliant in its simplicity it could be amended to do both)

http://groups.google.com/group/comp.soft-sys.sas/browse_thread/thread/9cffcbfd74c3e05e/83dd40bc93bd0bf7?hl=en&lnk=gst&q=YTD#83dd40bc93bd0bf7

 | 
Pages: 1
Prev: sas problem.
Next: Generating unique string identifier