DATA Step, Macro, Functions and more

Assigning values by sorting date values

Reply
Super Contributor
Posts: 272

Assigning values by sorting date values

Dear,

I need to calculate the vnum values  for visit=un. I assigned vnum values for visit=(Screen,base,treat,follow). How to get the out for visit=un based on order of dates. Please help.

 

 output needed:

id   date           visit           vnum
1     2016/01/01   screen        1
1    2016/01/15     un           1.1
1     2016/01/20    un          1.2
1     2016/02/01    base        2

1     2016/02/21     un         2.1
1     2016/03/01    treat        3
1      2016/04/01   follow        4

1     2016/04/12     un          4.1

1     2016/05/30     un           4.2

Super User
Posts: 17,861

Re: Assigning values by sorting date values

What have you tried?

 

Can you please post your sample data as a data step so we don't have to write that out as well?

Super User
Posts: 5,085

Re: Assigning values by sorting date values

It's probably easier to get rid of the existing values and start over.  Assuming that you have a series of IF/THEN statements to assign VNUM:

 

data want;

set have (drop=vnum);

*** existing IF/THEN statements;

if visit='un' then vnum + 0.1;

run;

 

It actually doesn't matter whether you use IF/THEN or some other method ... this should work either way.  But it's important to drop the existing VNUM variable as part of the process.

Super Contributor
Posts: 272

Re: Assigning values by sorting date values

Thanks for the help. For other visits(other than 'UN', as per specs the vnum values are assigned based on visit value. For visit='UN', i have to assign based date value. For obs 2 and 3, the date fall between obs 1 and 4. So the visitnum values are 1.1 and1.2. I have tried a few codes  by didnot get the output I need. Please help.

 

data1;

output needed:

id   date              visit           vnum
1     2016/01/01   screen        1
1    2016/01/15     un           1.1
1     2016/01/20    un          1.2
1     2016/02/01    base        2

1     2016/02/21     un         2.1
1     2016/03/01    treat        3
1      2016/04/01   follow        4

1     2016/04/12     un          4.1

1     2016/05/30     un           4.2

2      2016/06/01    screen     1

2      2016/06/12     un           1.1

2      2016/07/11      base       2

 

Super User
Posts: 5,085

Re: Assigning values by sorting date values

If you're going only by date values and the existence if "un", this might be the easiest way:

 

data want;

set have;

by id;

if first.id then vnum=0;

vnum + 0.1;

if vnum ne 'un' then vnum = ceil(vnum);

run;

 

It assumes that VNUM is not already part of the incoming data.

Super User
Posts: 17,861

Re: Assigning values by sorting date values

Post the code you've tried?
Super Contributor
Posts: 272

Re: Assigning values by sorting date values

Thanks for you help. Vnum values already there for visit other than 'UN'. I need to assign only vnum values for visit='UN' by comparing dates. Any suggestion. Thanks

Super Contributor
Posts: 272

Re: Assigning values by sorting date values

 

 

The following code i tried. It is very lengthy on top of my lengthy code. Please help if you can simplify the code. Thanks

 

data data2;

set data1;

if vnum=1 then date1=date;

else if vnum=2 then date2=date;

else if vnum=3 then date3=date;

elae if vnum=4 then date4=date;

run;

 

data3;

set data2(keep=id date1-date4);

run;

 

data4;

merge data data3;

by id;

run;

 

data5;

set data4;

if visit='UN' and    date1<date<date2 then seq=1;

if visit='UN' and    date2<date<date3 then seq=2;

if visit='UN' and    date3<date<date4 then seq=3;

if visit='UN' and    date4<date then seq=4;

run;

data data6;

set data5;

if visit ='UN';

run;

proc sort data=data6;

by id date;

run;

data data7;

set data6;

by id date;

retain vnum;

if first.id=1 then vnum=seq+0.1;

else vnum+0.1;

run;

 

Super User
Posts: 5,085

Re: Assigning values by sorting date values

Assuming you have already assigned VNUM for regular visits but VNUM is missing for "un" visits:

 

data want;

set have (rename=(vnum=oldvnum));

if oldvnum > . then vnum = oldvnum;

else vnum + 0.1;

drop oldvnum;

run;

Super Contributor
Posts: 272

Re: Assigning values by sorting date values

Dear,

 Thanks for your code. That helped me. It produced results I needed. But there is one more  I need to correct it.  For 5,6,7 observations vnum should be 2.1 because all the tests are done on same day for same test. with this code it gives 2.1,2.2,and 2.3. Please help how to correct it. Thanks.

data1;

output needed:

id   date              visit           vnum     test            seq
1     2016/01/01   screen        1         pulse         
1    2016/01/15     un           1.1         diabp           
1     2016/01/20    un          1.2          sysbp           

1     2016/02/01    base        2            diabp

1     2016/02/21     un         2.1           sysbp          1

1     2016/02/21     un          2.1         sysbp           2

1      2016/02/21    un           2.1        sysbp           3
1     2016/03/01    treat        3
1      2016/04/01   follow        4

1     2016/04/12     un          4.1

1     2016/05/30     un           4.2

2      2016/06/01    screen     1

2      2016/06/12     un           1.1

2      2016/07/11      base       2

Super User
Posts: 5,085

Re: Assigning values by sorting date values

There are several possibilities, but there are pitfalls too.  Here is the way I would recommend:

 

data want;

set have (rename=(vnum=oldvnum));

by id oldvnum date notsorted;

if oldvnum > . then vnum = oldvnum;

else if first.date then vnum + 0.1;

drop oldvnum;

run;

 

If you have two "un" in a row, but on separate dates, this will increment once for each date.  (Not sure if that's your intention or not, or whether it would matter in practice.)

Super Contributor
Posts: 272

Re: Assigning values by sorting date values

I just want to add one more thing. All the tests done visit='UN and 'same date should have same visitnum. Thanks. Please help.

Super User
Posts: 17,861

Re: Assigning values by sorting date values

It looks like you're doing some sort of transpose as well? Perhaps post the original data.

Super User
Posts: 17,861

Re: Assigning values by sorting date values

I'm assuming you have ID DATE and VISIT as variables in your data set. I'm assuming there is no variable called VNUM.

 

data want;
set have;

retain vnum;

if first.id then vnum=1;

if visit='un' then vnum=vnum + 0.1;
else vnum=floor(vnum)+1;

run;

Can you ever have more than 10 un within a visit type? If so, this won't work. 

Super User
Super User
Posts: 7,407

Re: Assigning values by sorting date values

Where is this data coming from?  It is obviously clinical data, so why does the source data not have the visit number identifier populated - this is standard process.  The reason is, whilst ordering by dates and then assigning a visit number might seem appropriate in certain circumstances it is not.  For example, if you have two timepoints on the same date, or the clinician deems a visit to be part of a previous visit window even when the date doesn't fit there.  You are going down a dangerous route of guessing, my I would get the source data updated by people who know, and are responsible for the testing.

This is assuming you are doing this from source or SDTM data.  If you are doing visit windowing in ADaM then this is a slightly different matter, SDTM should have all the raw information, but you will need to put in time windowing, observations carrried forward/back, and other such rules as defined in the SAP.

Ask a Question
Discussion stats
  • 14 replies
  • 377 views
  • 2 likes
  • 4 in conversation