topic Add the New changed obs only in SAS Programming

Add the New changed obs only

My_SAS — Tue, 17 Jan 2012 07:19:54 GMT

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

Add the New changed obs only

Ksharp — Tue, 17 Jan 2012 08:00:55 GMT

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

Re: Add the New changed obs only

My_SAS — Tue, 17 Jan 2012 08:09:11 GMT

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

Re: Add the New changed obs only

Ksharp — Tue, 17 Jan 2012 08:37:37 GMT

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

Add the New changed obs only

My_SAS — Tue, 17 Jan 2012 10:01:30 GMT

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

Add the New changed obs only

Ksharp — Tue, 17 Jan 2012 11:36:34 GMT

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

Add the New changed obs only

My_SAS — Tue, 17 Jan 2012 12:35:16 GMT

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

Add the New changed obs only

Ksharp — Tue, 17 Jan 2012 13:03:36 GMT

You can change it as you wish.

if _n_ le 4

Or You want to pick up a proportional obs?

Ksharp

Add the New changed obs only

Ksharp — Tue, 17 Jan 2012 13:35:24 GMT

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

Add the New changed obs only

My_SAS — Tue, 17 Jan 2012 14:07:47 GMT

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

Add the New changed obs only

art297 — Tue, 17 Jan 2012 15:13:47 GMT

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;

Add the New changed obs only

Ksharp — Wed, 18 Jan 2012 03:41:31 GMT

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