topic Re: New to SAS - simple problem in other languages - need help in SAS in SAS Programming

New to SAS - simple problem in other languages - need help in SAS

Longimanus — Tue, 03 Nov 2009 08:25:02 GMT

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)

Re: New to SAS - simple problem in other languages - need help in SAS

sbb — Tue, 03 Nov 2009 11:44:58 GMT

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

Re: New to SAS - simple problem in other languages - need help in SAS

GertNissen — Tue, 03 Nov 2009 11:55:41 GMT

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;

Re: New to SAS - simple problem in other languages - need help in SAS

Longimanus — Tue, 03 Nov 2009 12:11:37 GMT

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

Re: New to SAS - simple problem in other languages - need help in SAS

Peter_C — Tue, 03 Nov 2009 12:22:04 GMT

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

Re: New to SAS - simple problem in other languages - need help in SAS

Tim_SAS — Tue, 03 Nov 2009 14:22:07 GMT

See my post "Forum Markup" in the ODS and Base Reporting forum.

Re: New to SAS - simple problem in other languages - need help in SAS

Peter_C — Tue, 03 Nov 2009 22:36:39 GMT

Thank you Tim