Selecting specific dates for specific columns with proc report

[Bruce123]

Hello everyone

I wish to write a code, so it is possible to read the last couple of years performance in a table, so I want to pick the last date in the previous years.

My code is as follows:

PROC REPORT DATA = Performance_3_Aar HEADLINE NOWINDOWS SPLIT='*' Contents='' /* Removes node entirely */ spanrows /* Θn stor celle pr. gruppe vμrdi */

%ReportStyles

STYLE(column) = [background=white cellwidth=75pt]

STYLE(report)= [font_size=0.8 font_face=Times]; /* Makro med report indstillinger */

TITLE FONT=Times HEIGHT=4 Color=Black  "Afkast, performance og markedsvμrdi (pct. og mio. kr.) pσ forvaltere pr. %SYSFUNC(putn(&ReportDate,DDMMYY10.))";

FOOTNOTE FONT=Times HEIGHT=4 Color=Black 'Performance mσlt ift. forvalter benchmark.';

WHERE ToDate = &ReportDate AND mv >= 0.5 AND (ParentNodeName NE 'Aktier' AND ParentNodeName NE 'Obligationer' AND ParentNodeName NE 'Ejendomme');

COLUMNS OBS ParentNodeName ModelPortfolioName  Performance Performance_2010 Performance_2011 Performance_2012;

DEFINE OBS / GROUP NOPRINT;

DEFINE ParentNodeName / 'Type' GROUP;

DEFINE ModelPortfolioName / 'Forvalter' GROUP STYLE(column) = [cellwidth=/*150pt*/130pt] ORDER=DATA;;

DEFINE Performance / 'Perfor- * mance ÅTD' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Performance_2010 / 'Performance 2010' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Performance_2011 / 'Performance 2011' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Performance_2012 / 'Performance 2012' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

BREAK BEFORE ParentNodeName / SUPPRESS OL SKIP UL OL STYLE(SUMMARY)= {FONT_WEIGHT=BOLD BACKGROUND=LIGHTGREEN font_size=1};

RUN;

For the variables Performance_2010, Performance_2011 and Performance_2012 i need to write a statement, so that Sas will read the performance at the date 31/12/2010 in Column A, the date 31/12/2011 in column B and the date 31/12/2012 in column C in the dataset.

I am not sure how I should write this statement.

Can someone help me?

I would really appreciate it

Thanks

[Reeza]

Can you explain some more how your original data is structured? I don't quite follow what you want.

[Bruce123]

Hello

My dataset is basically structured with a date variable called todate. And other that some variables with the performances from differtent years in different columns.

And i want to pick the date 31/12 in these columns.

So i want to write a statement such as:

Where todate=reportdate in column performance (this is the column for performance for 2013) and todate =mdy(12, 31, 2010) in colmun performance_2010 and todate = mdy(31, 12, 2011) in column performance_2011.

And so on for the other years.

But i am not sure how to write this statement in sas.

[BrunoMueller]

Hi Bruce123

One step to approach this is to use Proc TRANSPOSE to restructure your data, and then use some macro logic to build the Proc REPORT code, see sample below.

data have;
  do group = "A", "B", "C";
    do year  = 2000 to 2013;
      toDate = mdy(1, 1, year);
      performance = ceil( rand("uniform") * 1000 );
      output;
    end;
  end;

  format toDate date9.;
run;

%macro myReport(startYear=2009, endYear=2012);
  %local i;

  proc transpose
    data=have
    out=want
    prefix=performance_
  ;
    where year between &startYear and &endYear;
    by group;
    id year;
    var performance;
  run;

  title "Performance from &startYear to &endYear";
  proc report data=want nowindows;
    column
      group
      performance_&endYear

      %do i = &startYear %to (&endYear -1);
       performance_&i
      %end;
    ;
    define group / display;
    define performance_&endYear / display "Performance";

    %do i = &startYear %to (&endYear -1);
      define performance_&i / display /* "Performance &i" */;
    %end;
  run;
  title;

%mend;

%myReport()

[Bruce123]

Hello

Thanks for your help.

I will try this as soon as possible