how to add a target line in EG SAS query

Reply
New Contributor
Posts: 2

how to add a target line in EG SAS query

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?

Super Contributor
Posts: 644

Re: how to add a target line in EG SAS query

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

New Contributor
Posts: 2

Re: how to add a target line in EG SAS query

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?

MonthTarget
Jul0.4
Aug0.8
Sep1.2
Oct1.6
Nov2
Dec/Jan2.8
Feb3.2
Mar3.6
Apr4
May4.4
Jun4.8
Super Contributor
Posts: 644

Re: how to add a target line in EG SAS query

I would do it in Excel or as a tab separated text file, like this, assuming you have more than one Department.

DepartmentYearMonthTarget
Widgets201370.4
Widgets201380.8
Widgets201391.2
Widgets2013101.6
Widgets2013112
Widgets201412.8
Widgets201423.2
Widgets201433.6
Widgets201444
Widgets201454.4
Widgets201464.8
Wotsits20137etc

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

Ask a Question
Discussion stats
  • 3 replies
  • 236 views
  • 0 likes
  • 2 in conversation