Prev: Should I get SAS Learning Edition 4.1?
Next: How do you do this?
From: Jinto83 on 6 Jun 2010 22:12
Hi, suppose I have a file like this:

OriginalFile:
source make year sale
1 toyota 1997 100
1 toyota 1998 .
1 ford 1997 300
1 ford 1998 400
2 toyota 1997 100
2 toyota 1998 200
2 ford 1997 300
2 ford 1998 450

So I have two data sources for the annual sales.Source1 and Source2.

Source2 has sale data on toyota in 1998 whereas source1 doesn't .
Also source1 and source2 report exactly the same sales data on 1997Toyota and 1997Ford.
However, source1 and source2 conflict each other on 1997Ford sales data.

I want to generate a table that merges the information from source1 with that of source2, and when source1 and source2 report different values, eg in 1998 Ford, assign conflict_flag to 1 while leaving the sale figure blank.


DesiredOutput:
conflict_flag make year sale
0 toyota 1997 100
0 toyota 1998 200
0 ford 1997 300
1 ford 1998 .



Thank you very much


--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Patrick on 7 Jun 2010 07:03
How comes that these teachers always use car examples as assignments
for their students? Is the car industry bribing them :-)

To solve it:
Split your source table into 2 tables and then merge these 2 tables
over make and year.
You can use a data step merge or a SQL join for this.
In a data step merge: Just rename in one of the input tables the sale
variable and then compare the 2 variables to generate your flag.

HTH
Patrick
From: PJ on 7 Jun 2010 08:46
Another option is using retain.

proc sort data = have ;
by make year source;
run;

data want(drop=source sale rename=(psale = sale));
set have;
retain psale 0;
by make year source;
if first.year then psale = sale;
else do;
if psale = sale or psale = . then
do;
conflict = 0;
psale = sale;
output;
end;
else
do;
conflict = 1;
psale = .;
output;
end;
end;
put _all_;
run;

Cheers,

P.J
 | 
Pages: 1
Prev: Should I get SAS Learning Edition 4.1?
Next: How do you do this?