05-27-2014 10:39 PM
I already have a SAS dataset created and I am using the data to create my report.
I am really struggling with the target line. Mainly because it is not in the SAS dataset and it is a yearly target broken down into 12 months.
Any ideas on how to do this?
05-28-2014 08:14 PM
If you are familiar with Proc Report you could create computed columns for targets and over/under results.
Alternately, is your target an overall target for the organisation? Or do you have departmental targets? If there are departmental targets you could create a table of targets by department and merge it back to the dataset you have. If it is a single target you could use a macro variable :
%Let AnnualTarget = 1000000 ; /* Insert current annual target */
%Let Target = %Sysevalf (&AnnualTarget / 12) ; /* Monthly target */
Data want ;
Set have ;
Retain target &Target ;
<other calculations, as required >
05-29-2014 07:30 PM
Thanks Richard. I have departmental targets as shown in the table below. How can I create a table in SAS EG with out importing a spreadsheet into SAS?
05-29-2014 09:26 PM
I would do it in Excel or as a tab separated text file, like this, assuming you have more than one Department.
Note that I have rolled Dec/Jan into month 1 of 2014 assuming Dec/Jan means a single report covering both December and January. If not, provide a separate target row for each month.
You can then merge with your existing data (simplify if you do not need to merge Dec/Jan):
Proc SQL ;
Create table want as
When Month(Date) = 12 then mdy(1,1, Year(Date)+1)
end as ReptDate
From have hav
, Targets tgt
Where tgt.Department = hav.Department
and tgt.Year = Year(calculated ReptDate)
and tgt.Month = Month(calculated ReptDate)
[untested code - you may need to create ReptDate in a separate query]