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

how to add a target line in EG SAS query

krishnank — Wed, 28 May 2014 02:39:19 GMT

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?

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

RichardinOz — Thu, 29 May 2014 00:14:00 GMT

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 ;

Run ;

Richard

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

krishnank — Thu, 29 May 2014 23:30:01 GMT

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

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

RichardinOz — Fri, 30 May 2014 01:26:05 GMT

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