BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

14 REPLIES 14
Reeza
Super User

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?

Astounding
PROC Star

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.

knveraraju91
Barite | Level 11

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

 

Astounding
PROC Star

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.

Reeza
Super User
Post the code you've tried?
knveraraju91
Barite | Level 11

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

knveraraju91
Barite | Level 11

 

 

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;

 

Astounding
PROC Star

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;

knveraraju91
Barite | Level 11

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

Astounding
PROC Star

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

knveraraju91
Barite | Level 11

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

Reeza
Super User

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

Reeza
Super User

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 14 replies
  • 1253 views
  • 2 likes
  • 4 in conversation