Add the New changed obs only

[My_SAS]

Reg to keep only new once

i am having Table Test and the incr will keep on adding but i wnat to add the obs based on id and only the no is changes observations.

Data Test;
input id no;
cards;
1 10
2 11
3 12
4 13
run;

data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
run;

Output Test Table

1 10
2 11
3 12
4 13
1 14
2 15

[Ksharp]

How about:

Data Test;
input id no;
cards;
1 10
2 11
3 12
4 13
;
run;


data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
;
run;

proc sql;
create table want as
 select * from test
 union all corresponding
 (select * from inc
   except
  select * from test) ;
quit;

Ksharp

[My_SAS]

Thqs Ksharp  small Modification can you help me in this

Data Test;
input id no sdate ddmmyy10. Endate ddmmyy10.;
Format sdate Endate ddmmyy10.;
cards;
1 10 13/01/2011 .
2 11 13/01/2011 .
3 12 13/01/2011 .
4 13 13/01/2011 .
;
run;

data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
;
run;

For the Inc Table the Sdate and Edate will be sysdate (Today Date)

Output Test:
ID No sdate     Edate
1 10 13/01/2012 17/01/2012
2 11 13/01/2012 17/01/2012
3 12 13/01/2012  .
4 13 13/01/2012  .
1 14 17/01/2012
2 15 17/01/2012

[Ksharp]

OK.

Data Test;
input id no sdate : ddmmyy10. Endate : ddmmyy10.;
Format sdate Endate ddmmyy10.;
cards;
1 10 13/01/2011 .
2 11 13/01/2011 .
3 12 13/01/2011 .
4 13 13/01/2011 .
;
run;

data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
;
run;

proc sql;
create table temp as
 select * from inc
   except
  select * from test(keep=id no) ;
quit;
data test;
 set test;
 if _n_ le 2 then Endate="&sysdate"d;
run;
data temp;
 set temp;
 sdate="&sysdate"d;
run;
proc append base=test data=temp force nowarn;run;

Ksharp

[My_SAS]

But you have kept

data test;

set test;

if _n_ le 2 then Endate="&sysdate"d;

run;

As i know only two observations have changed you have kept _n_ le 2 if the count increase how can i handle them

[Ksharp]

You want all the value of Endate to be SYSDATE?

Then remove the condition.

data test;

set test;

Endate="&sysdate"d;

run;

data temp;

set temp;

sdate="&sysdate"d;

Endate="&sysdate"d;

run;

Ksharp

[My_SAS]

I want the output like this

Output Test:
ID No sdate     Edate
1 10 13/01/2012 17/01/2012
2 11 13/01/2012 17/01/2012
3 12 13/01/2012  .
4 13 13/01/2012  .
1 14 17/01/2012
2 15 17/01/2012

But in the code at one poing you have written

if _n_ le 2 then Endate="&sysdate"d;

as i am having 2 obs i know and can keep _n_ le 2 if the count increae how can i do

[Ksharp]

You can change it as you wish.

if _n_ le 4 

Or You want to pick up a proportional obs?

Ksharp

[Ksharp · Accepted solution]

Hello. My_SAS.

Sorry ! I understood what you mean now.

Data Test;
input id no sdate : ddmmyy10. Endate : ddmmyy10.;
Format sdate Endate ddmmyy10.;
cards;
1 10 13/01/2011 .
2 11 13/01/2011 .
3 12 13/01/2011 .
4 13 13/01/2011 .
;
run;

data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
;
run;

proc sql;
create table temp as
 select * from inc
   except
  select * from test(keep=id no) ;
quit;
data test;
if _n_ eq 1 then do;
 length id 8;
 declare hash h(dataset:'temp');
  h.definekey('id');
  h.definedone();
  end;
 set test;
 if h.check()=0 then Endate="&sysdate"d;
run;

data temp;
 set temp;
 sdate="&sysdate"d;
run;
proc append base=test data=temp force nowarn;run;

Ksharp

[My_SAS]

Thqs it worked ,is there any way to do this with out Hash keys

[art297]

I didn't read your specs fully, but the following produces the same result as long as both of your input files (i.e., test and inc) are in id order:

data test (drop=b_:);

  merge test (in=ina) inc (in=inb rename=(no=b_no));

  by id;

  if ina and inb then do;

    if no ne b_no then do;

      Endate=today();

      output;

      call missing(Endate);

      sdate=today();

      no=b_no;

      output;

    end;

    else output;

  end;

  else if ina then output;

run;

proc sort data=test;

  by no;

run;

[Ksharp]

OK. Here is a pure SQL method. If you like it.

Data Test;
input id no sdate : ddmmyy10. Endate : ddmmyy10.;
Format sdate Endate ddmmyy10.;
cards;
1 10 13/01/2011 .
2 11 13/01/2011 .
3 12 13/01/2011 .
4 13 13/01/2011 .
;
run;

data inc;
input id no;
cards;
1 14
2 15
3 12
4 13
;
run;
proc sql;
create table temp as
 select * from inc
   except
  select * from test(keep=id no) ;

create table want as
 select a.id,a.no,sdate,case when missing(b.id) then . else "&sysdate"d  end as Endate  format=ddmmyy10.
  from test as a left join temp as b 
   on a.id = b.id 

 outer union  corresponding

 select *,"&sysdate"d as sdate  from temp
;

quit;

Ksharp