topic Re: Selecting specific dates for specific columns with proc report in SAS Programming

Selecting specific dates for specific columns with proc report

Bruce123 — Tue, 17 Sep 2013 21:10:58 GMT

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

Re: Selecting specific dates for specific columns with proc report

Reeza — Wed, 18 Sep 2013 00:05:27 GMT

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

Re: Selecting specific dates for specific columns with proc report

Bruce123 — Wed, 18 Sep 2013 07:58:06 GMT

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.

Re: Selecting specific dates for specific columns with proc report

BrunoMueller — Wed, 18 Sep 2013 08:44:16 GMT

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()

Re: Selecting specific dates for specific columns with proc report

Bruce123 — Wed, 18 Sep 2013 12:51:43 GMT

Hello

Thanks for your help.

I will try this as soon as possible