BookmarkSubscribeRSS Feed
Longimanus
Obsidian | Level 7
Hi to all of you out there!

I need to either 1. update or 2. retrieve erroneous observations of a table (and write them to another table) based on a previously read observation. I have (yet) no clue how I save variables of a previously read observation in a data step.

I have here an exampe table which can illustrate the problem I have to solve. There are 3 observations for two products. The prices are stated and the period-intervals where there prices were relevant. Obs. 2 and 5 are erroneous. Why? Well because the valid-from date should be equal to the PREVIOUS valid-to date + 1. There are obviously gaps in 2 and 5.

Obs Product_no price valid_from valid_to
1 4711 100 01-01-2000 31-01-2002
2 4711 150 15-02-2002 03-06-2008
3 4711 125 04-06-2008 31-12-9999
4 5000 1500 01-01-2007 31-12-2007
5 5000 1575 01-02-2008 31-12-2008
6 5000 2000 01-01-2009 31-12-9999

(!!! I just saw in a preview of this message that my neetly spaced columns are packed together in the posted version! 😞 Therefore: if you think you can help me. please e-mail to menno.hilverdink@sebkort.dk and I'll send you something more clear ...)

I need to write a program that can "catch" these entries. Even better a new table which I can later use for update (with PROC SQL for example) that contains the following variables: Product_no, valid_from, new_valid_from

Any of you out there who likes to solve this (for me) riddle? You'd make me a happy man! 🙂

Cheers!
Menno Hilverdink (Copenhagen, Denmark)
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Using a DATA step approach, one technique would be to create/maintain an "exception" SAS file in addition to your primary SAS data table(s). The DATA statement can specify more than one output file and use some type of IF/THEN/OUTPUT logic statements to re-direct (your word "catch") observations that do not meet your criteria and send them to a separate table for post-processing of some sort.

You will need to decide how to handle the exceptions, for how long to keep them in the exception file, possibly track the number of executions when a particular exception is generated, and produce some exception-reporting, and possibly develop into your application a method of correcting exceptions, if feasible.

Scott Barry
SBBWorks, Inc.

Step-by-Step Programming with Base SAS(R) Software - Understanding DATA Step Processing
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001304324.htm


SAS(R) 9.2 Language Reference: Concepts - The Data Step
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a000992092.htm


SAS(R) 9.2 Language Reference: Concepts - About DATA Step Execution
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001331122.htm
Longimanus
Obsidian | Level 7
Thanx for your feedback. I will look into it! 🙂

I did solve my problem already like this (with input from a third party):

data mylib.erroneous_obs;
set mylib.error_from_date;
by product valid_from_sas;
retain dt 0;
If first.product then do;
dt = valid_to_sas;
end;
else do;
if valid_from_sas ne dt + 1 then do;
new_valid_dt = dt + 1;
output;
end;
dt = valid_to_sas;
end;
format new_valid_dt ddmmyyd10.;
drop dt;
run;

Off course the variables name differ ... but surely u can spot the approach. :-) Message was edited by: Longimanus
GertNissen
Barite | Level 11
In SAS you have several ways to solve this - i.e. have a look at RETAIN or LAG().

This little mock-up code will show you one path you could walk down.

data input;
format valid_from valid_to ddmmyy10.;
input Obs Product_no price valid_from :ddmmyy10. valid_to :ddmmyy10.;
datalines;
1 4711 100 01-01-2000 31-01-2002
2 4711 150 15-02-2002 03-06-2008
3 4711 125 04-06-2008 31-12-9999
4 5000 1500 01-01-2007 31-12-2007
5 5000 1575 01-02-2008 31-12-2008
6 5000 2000 01-01-2009 31-12-9999
;
run;

proc sort data=input;
by Product_no descending valid_from valid_to;
run;

data output;
format prev new_valid_to ddmmyy10.;
set input;
by Product_no;
if not first.product_no then prev = lag1(valid_from-1);
if prev ne . then new_valid_to = prev;
else new_valid_to = valid_to;
run;

proc sort data=output;
by Product_no valid_from valid_to;
run;
Peter_C
Rhodochrosite | Level 12
Menno

only in response to:
> !! I just saw in a preview of this message that my
> neetly spaced columns are packed together in the
> posted version! 😞 Therefore: if you think you can
> help me. please >

somewhere among the message history on the forums should exist a guide to support posters who seek "unformatted", allowing a table to keep its shape, and code to keep its indentation.
It should describe "tagging". That is preceeding formatted code with {pre} (but using square brackets instead of curly braces), and reinstating normal message formatting with the {/pre]
Other message formatting tags I am aware of [b for bold [i for italic[/i


Some helpful poster might be able to provide a link to the range of possibilites, (please someone?)
I would really appreciate guidance including what we would need to show the usual symbols rather GE and LE in out code
Tim_SAS
Barite | Level 11
See my post "Forum Markup" in the ODS and Base Reporting forum.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 767 views
  • 0 likes
  • 5 in conversation