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
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
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
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
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
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
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
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
You can change it as you wish.
if _n_ le 4
Or You want to pick up a proportional obs?
Ksharp
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
Thqs it worked ,is there any way to do this with out Hash keys
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.