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?
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 >
Run ;
Richard
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?
Month | Target |
Jul | 0.4 |
Aug | 0.8 |
Sep | 1.2 |
Oct | 1.6 |
Nov | 2 |
Dec/Jan | 2.8 |
Feb | 3.2 |
Mar | 3.6 |
Apr | 4 |
May | 4.4 |
Jun | 4.8 |
I would do it in Excel or as a tab separated text file, like this, assuming you have more than one Department.
Department | Year | Month | Target |
Widgets | 2013 | 7 | 0.4 |
Widgets | 2013 | 8 | 0.8 |
Widgets | 2013 | 9 | 1.2 |
Widgets | 2013 | 10 | 1.6 |
Widgets | 2013 | 11 | 2 |
Widgets | 2014 | 1 | 2.8 |
Widgets | 2014 | 2 | 3.2 |
Widgets | 2014 | 3 | 3.6 |
Widgets | 2014 | 4 | 4 |
Widgets | 2014 | 5 | 4.4 |
Widgets | 2014 | 6 | 4.8 |
Wotsits | 2013 | 7 | etc |
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
Select hav.*
, Case
When Month(Date) = 12 then mdy(1,1, Year(Date)+1)
else date
end as ReptDate
, tgt.Target
From have hav
, Targets tgt
Where tgt.Department = hav.Department
and tgt.Year = Year(calculated ReptDate)
and tgt.Month = Month(calculated ReptDate)
;
Quit ;
[untested code - you may need to create ReptDate in a separate query]
Richard
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.