need help with do loop

[DanD999]

I've tried a variety of things. I can get the output to one row per r_id which is what I want but the result is not correct.

If there is no strt_dt for a r_id then we want the earliest creat_dt.

if there is a strt_dt for a r_id we want the earliest strt_dt.

This is what the result should look like

J1178 03/05/2014
J2256 03/22/2014
J2256 03/26/2014
J3363 03/19/2014
J4444 03/24/2014
J5522 03/24/2014
J8434 03/24/2014

data have;
input @1 r_id $5. @7 creat_dttm mmddyy10. @18  strt_dt mmddyy10. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014 
J2256 03/26/2014 
J3363 03/18/2014 03/19/2014
J8434 03/19/2014 
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014 
J8434 03/11/2014 
J4444 03/21/2014 03/24/2014
J5522 03/24/2014 
J2256 03/26/2014 03/22/2014  
J3363 03/10/2014 03/21/2014
J1178 03/24/2014 
;
run;

proc sort data=have;
	by r_id strt_dt creat_dttm;
run;



data new;
	set have;
	by r_id strt_dt creat_dttm;
	format newdate maybe_dt mmddyy10.;
	if first.r_id then do;
		retain newdate .;
		retain maybe_dt .;
	end;
 	if first.r_id and strt_dt =. then do;
		maybe_dt=creat_dttm;
		put 'new' newdate;
	end;
	else if first.r_id and strt_dt !.  then newdate=strt_dt;
	else if newdate =. then newdate=strt_dt;
	if newdate =. then newdate=strt_dt;
	if last.r_id and newdate =. then do;
		newdate=maybe_dt;
		output;
	end;
/*	else if newdate !. then do;*/
/*		newdate=maybe_dt;*/
/*		output;*/
/*	end;*/
run;

Any help is greatly appreciated. Thanks.

[Shmuel · Accepted solution]

Try next code:

data have;
input @1 r_id $5. @7 creat_dttm mmddyy10. @18  strt_dt mmddyy10. ;
format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014 
J2256 03/26/2014 
J3363 03/18/2014 03/19/2014
J8434 03/19/2014 
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014 
J8434 03/11/2014 
J4444 03/21/2014 03/24/2014
J5522 03/24/2014 
J2256 03/26/2014 03/22/2014  
J3363 03/10/2014 03/21/2014
J1178 03/24/2014 
;
run;

proc sort data=have;
	by r_id creat_dttm strt_dt;
run;

data new;
 set have;
  by r_id;
    retain newdate flag;
	format newdate  mmddyy10.;
	if first.r_id then do;
	   newdate = creat_dttm;
	   flag = 0;
	end;
	if not missing(strt_dt) and flag=0
	then do; newdate = strt_dt; flag=1; end;
	if last.r_id then output;
	keep r_id newdate;
run;

[DanD999]

Excellent. That's it. Thank you so much for the help and fast response.

[Tom]

---

@DanD999 wrote:

...

If there is no strt_dt for a r_id then we want the earliest creat_dt.

if there is a strt_dt for a r_id we want the earliest strt_dt.

---

If should be pretty straight forward to translate that into SQL.

data have;
  input  r_id :$5. creat_dttm :mmddyy. strt_dt :mmddyy. ;
  format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014 .
J2256 03/26/2014 .
J3363 03/18/2014 03/19/2014
J8434 03/19/2014 .
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014 .
J8434 03/11/2014 .
J4444 03/21/2014 03/24/2014
J5522 03/24/2014 .
J2256 03/26/2014 03/22/2014  
J3363 03/10/2014 03/21/2014
J1178 03/24/2014 .
;

proc sql noprint;
create table want as 
  select r_id
       , coalesce(min(strt_dt),min(creat_dttm)) as newdate format=mmddyy10.
  from have
  group by r_id
;
quit;

Results:

Obs    r_id        newdate

 1     J1178    03/05/2014
 2     J2256    03/22/2014
 3     J3363    03/19/2014
 4     J4444    03/24/2014
 5     J5522    03/24/2014
 6     J8434    03/24/2014

[DanD999]

---

@Tom wrote:

---

@DanD999 wrote:

...

If there is no strt_dt for a r_id then we want the earliest creat_dt.

if there is a strt_dt for a r_id we want the earliest strt_dt.

---

If should be pretty straight forward to translate that into SQL.

data have;
  input  r_id :$5. creat_dttm :mmddyy. strt_dt :mmddyy. ;
  format creat_dttm strt_dt mmddyy10.;
cards;
J3363 03/17/2014 .
J2256 03/26/2014 .
J3363 03/18/2014 03/19/2014
J8434 03/19/2014 .
J2256 03/24/2014 04/29/2014
J8434 03/20/2014 03/24/2014
J1178 03/05/2014 .
J8434 03/11/2014 .
J4444 03/21/2014 03/24/2014
J5522 03/24/2014 .
J2256 03/26/2014 03/22/2014  
J3363 03/10/2014 03/21/2014
J1178 03/24/2014 .
;

proc sql noprint;
create table want as 
  select r_id
       , coalesce(min(strt_dt),min(creat_dttm)) as newdate format=mmddyy10.
  from have
  group by r_id
;
quit;

Results:

Obs    r_id        newdate

 1     J1178    03/05/2014
 2     J2256    03/22/2014
 3     J3363    03/19/2014
 4     J4444    03/24/2014
 5     J5522    03/24/2014
 6     J8434    03/24/2014

---

That does also work. Thanks.

[Cynthia_sas]

Hi:

  I'm confused by your data and what you say you want:

Why do you show only 1 row per ID in your desired output but show 2 rows for J2256? It is not clear to me whether you expect to see 13 rows in the final output, 6 rows in the final output or 7 rows in the final output. Can you clarify?

Cynthia

[DanD999]

---

@Cynthia_sas wrote:

Hi:

  I'm confused by your data and what you say you want:

Why do you show only 1 row per ID in your desired output but show 2 rows for J2256? It is not clear to me whether you expect to see 13 rows in the final output, 6 rows in the final output or 7 rows in the final output. Can you clarify?

Cynthia

---

I did say in the initial post "I can get the output to one row per r_id which is what I want"

Thanks for the reply.