topic Re: Is it possible to use more datasets in one proc report in SAS Programming

Is it possible to use more datasets in one proc report

Bruce123 — Wed, 18 Sep 2013 11:26:04 GMT

Hello

I was wonderings if is possible to use more datasets when doing a proc report (it has to be in the same proc report).

If i have the following code:

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;

Is it possible to when defining variables that I define them from another dataset. So i.e. that when defining the variable performance_2010 the data is taken from another dataset then stated in the start of the code. And will it be possible to add a "where statement" to each dataset?

Re: Is it possible to use more datasets in one proc report

BrunoMueller — Wed, 18 Sep 2013 11:49:17 GMT

Proc REPORT can only work with one SAS Data Set, but you may use macro logic to run Proc REPORT several times with different SAS Data Sets

Re: Is it possible to use more datasets in one proc report

esjackso — Wed, 18 Sep 2013 12:43:38 GMT

Can you merge the data into the single dataset? Just based on the code you provided it seems you need the following variables all in one dataset that feeds report:

ToDate mv ParentNodeName OBS ModelPortfolioName Performance Performance_2010 Performance_2011 Performance_2012;

Is there reason this would not work?

Re: Is it possible to use more datasets in one proc report

Bruce123 — Wed, 18 Sep 2013 13:03:22 GMT

Hello

Yes, the data is merged.

But the problem is that i have to pick the last date in the performance variable for the previous years, i.e. i have to pick 31/12/2010 in performance_2010, pick the date 31/12/2011 in performance_2011 and so on and at the same time get the newest date in the variable performance, which is contains the performance for 2013.

I was informed that it was not possible to write a "where statement" and was hoping using more datasets in one proc report was possible

Re: Is it possible to use more datasets in one proc report

esjackso — Wed, 18 Sep 2013 13:36:15 GMT

Apparently Im not understanding what is needed, because it still sounds like you need to pre process the data before pushing it through report.

You should be able to select the dates you need through a data step prior to the report.

Re: Is it possible to use more datasets in one proc report

Bruce123 — Wed, 18 Sep 2013 13:45:08 GMT

Hello

I have one dataset where the important variable are: ParentNodeName, ModelPortifolioName, ToDate, performance_2010, performance_2011, performance_2012 and performance (this is performance for 2013).

These are all in one dataset.

I kind of want to write a "where statement" in a proc report statement that says: Where todate= &ReportDate and Where ToDate = mdy(12, 31, 2010) in Column performance_2010 and Where ToDate = mdy(12, 31, 2011) in Column performance_2011 and Where ToDate = mdy(12, 31, 2012) in column Performance_2012 and so on.

But as I am informed this is not possible?

Re: Is it possible to use more datasets in one proc report

esjackso — Wed, 18 Sep 2013 14:07:08 GMT

Not trying to be a pain, but I think there is a solution for this. I guess the confusion is ... if the data is truly merged then why are the extra datasets needed? If it isnt merged they why could you not select the data you need in the report from the various sources then merge them to create the source for the report?

Re: Is it possible to use more datasets in one proc report

Bruce123 — Wed, 18 Sep 2013 14:59:36 GMT

Hello

You are no pain my freind

The data is merged, but I can easily make an unmerged dataset. Since I am able to do this, I wanted to know if it was possible to use more datasets in en proc report, since it would have solved my problem.

So, I have both merged and unmerged data.

But the problem is that when I use Proc Report, I want to pick specfik dates. Performance_2010 has the performance for 2010 and I want to pick the performance at the date 31/12/2010. Performance_2011 has the performance for 2011, and i want to pick the date 31/12/2011. To do this I have to writ a "where statement". And it is here that my problem lies.

I want to pick the newest date (&reportdate) in the variable performance, and the same time pick the date 31/12/2010 in performance_2010 and 31/12/2011 in performance_2011 and so on.

So, the problem is not in the data, but how the "where statement" should be written.

I was hoping that it was possible to use more datasets in proc report, and thus making an umerged dataset, and using that. But that is apparently not possible.