BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
tan-wongv
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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;
tan-wongv
Obsidian | Level 7

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

@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.

tan-wongv
Obsidian | Level 7

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 447 views
  • 0 likes
  • 3 in conversation