BookmarkSubscribeRSS Feed
krishnank
Calcite | Level 5

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?

3 REPLIES 3
RichardinOz
Quartz | Level 8

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
Calcite | Level 5

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
RichardinOz
Quartz | Level 8

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

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 560 views
  • 0 likes
  • 2 in conversation