BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
My_SAS
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

11 REPLIES 11
Ksharp
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

You can change it as you wish.

if _n_ le 4 

Or You want to pick up a proportional obs?

Ksharp

Ksharp
Super User

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
Calcite | Level 5

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

art297
Opal | Level 21

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1636 views
  • 0 likes
  • 3 in conversation