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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: