how to add a target line in EG SAS query

[krishnank]

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?

[RichardinOz]

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

[krishnank]

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

[RichardinOz]

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