topic Re: Generate T1 and T2 base on visit date in New SAS User

Generate T1 and T2 base on visit date

tan-wongv — Fri, 24 May 2024 00:50:11 GMT

I have a data set with DMRN and date of the visit (DX_Date). I would like to generate the the start and stop time or T1 and T2 based on each visit date. Could you please help me with the code?

Here is how the data look like:

DMRN	DX_DATE
31	09MAY2017
31	30MAY2017
31	06APR2018
31	07FEB2019
33	02JUN2017
33	04DEC2017
33	11JUN2018

This is how I want to outcome be like:

DMRN	T1	T2
31	09MAY2017	30MAY2017
31	30MAY2017	06APR2018
31	06APR2018	07FEB2019
33	02JUN2017	04DEC2017
33	04DEC2017	11JUN2018

Re: Generate T1 and T2 base on visit date

Patrick — Fri, 24 May 2024 02:31:13 GMT

Here one option.

data have;
 input DMRN DX_DATE:date9.;
 format dx_date date9.;
 datalines;
31 09MAY2017
31 30MAY2017
31 06APR2018
31 07FEB2019
32 01JUN2017
33 02JUN2017
33 04DEC2017
33 11JUN2018
;
data want;
  set have;
  by dmrn dx_date;
  format t1 t2 date9.;
  retain t1;

  if first.dmrn and last.dmrn then 
    do;
      t1=dx_date;
      output;
    end;
  else if not first.dmrn then
    do;
      t2=dx_date;
      output;
    end;
  t1=dx_date;
run;

proc print data=want;
run;

Re: Generate T1 and T2 base on visit date

tan-wongv — Fri, 24 May 2024 04:27:47 GMT

Thank you so much for your help!
Could you also please help me with the code if I want to generate t1 t2 t3 and t4?

The outcome would be:

DMRN	t1	t2	t3	t4
31	09MAY2017	30MAY2017	06APR2018	07FEB2019
33	02JUN2017	04DEC2017	11JUN2018	.

Re: Generate T1 and T2 base on visit date

ballardw — Fri, 24 May 2024 06:02:58 GMT

@tan-wongv wrote:

Thank you so much for your help!
Could you also please help me with the code if I want to generate t1 t2 t3 and t4?

The outcome would be:

DMRN t1 t2 t3 t4

31 09MAY2017 30MAY2017 06APR2018 07FEB2019

33 02JUN2017 04DEC2017 11JUN2018 .

This would do the basic reshaping of the data if the data is at least grouped by DMRN:

proc transpose data=have
   out=want (drop=_name_)
   prefix=t
   ;
   by dmrn notsorted;
   var dx_date;
run;

You would have to describe the logic why DRMN=32 is not in your want set and apply that logic to the Want to remove it (I might guess if the number of T variables with not missing values (function N) is greater than 1.

Re: Generate T1 and T2 base on visit date

tan-wongv — Fri, 24 May 2024 15:53:42 GMT

Thank you for your guidance! DMRN is like an ID. I don't have all the number since this is the subset from the original dataset.