Excluding observations that exceed a specific date

Reply
Contributor
Posts: 47

Excluding observations that exceed a specific date

Hello everyone

I am quite new to the SAS programming area, so I would appreciate your help.

I want to calculate a variable where i sum its value respectively for the last 365 days, 730 days and 1095 days, each in its own column.

My current code can be seen below, where only the second last line is what is relevant for this thread. At the moment I have only included the code in which I am trying to find the last 365 days, since I am assumming that it will be the same code that I will have to write, just with a different day-constranit, when I want to make the other two columns.

PROC REPORT DATA = Afkastmodelportfoliotothistory 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.5 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 %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 in ('Aktier', 'Obligationer', 'Ejendomme');

COLUMNS OBS ParentNodeName ModelPortfolioName Performance;

DEFINE OBS / GROUP NOPRINT;

DEFINE ParentNodeName/ 'Class' GROUP;

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

DEFINE Performance 1 year = sum(of Performance - Performance)>-365 / 'Performance seneste 1 år' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

RUN;

As you can see the "define performance 1 year" line is that one that I am having problems with. The name of the variable is "Performance" from where SAS brings the data from the work-file.

Can anyone help me?

I would really appreciate it

Thanks Smiley Happy

Super User
Posts: 5,256

Re: Excluding observations that exceed a specific date

I'm no PROC REPORT export, so I would suggest that you prepare the data on beforehand in some manner. Exactly how is difficult to without seeing the source data.

Data never sleeps
Contributor
Posts: 47

Re: Excluding observations that exceed a specific date

I receive new data each day so it would be perferable to have sas do it so it can run it each day

Super User
Super User
Posts: 6,499

Re: Excluding observations that exceed a specific date

If you want to exclude observations based on dates then you need have a date variable that you can reference.

If you want to generate separate variables with sums over different time intervals then that sounds like something that you would do in a data step or a proc sql code.  Once you have made your calculations you can use PROC REPORT or other tools to produce a pretty print out.

Contributor
Posts: 47

Re: Excluding observations that exceed a specific date

Thanks

I will try this

But as stated i am quite new. Could you give an example of how the code would look if i used proc sql.

I would really appreciate it

Super User
Posts: 5,256

Re: Excluding observations that exceed a specific date

Condition in SQL could look like this:

...

select case when todate between &reportdate -365 and &reportdate then Performance else . end as Performance1year

...

Data never sleeps
Contributor
Posts: 47

Re: Excluding observations that exceed a specific date

Thanks

I will try this when i get the chance

Ask a Question
Discussion stats
  • 6 replies
  • 274 views
  • 6 likes
  • 3 in conversation