how to get the data value in one column to other column logically

[Sri_devi]

Hello experts,

i have below data as. My question here is how to get the dose data value to all the missing data row in the new dose column. for example i want for 1001 subject the dose 2.5 should be reflected to the row above to it on the new dose column. Please refer the required output as in table 2

table 1:

SubjectID	Dose	New dose
1001	.
1001	.
1001	.
1001	2.5	5
1001	.
1001	.
1001	.
1001	5	7.5
1001	.
1001	.
1001	7.5	12.5
1002	.
1002	.
1002	.
1002	10	12.5
1002	.
1002	.
1002	12.5	7.5
1003	.
1003	.
1003	.
1003	10	5
1003	.
1003	.

i want the data as in below table

table 2:

SubjectID	Dose	New dose
1001	.	2.5
1001	.	2.5
1001	.	2.5
1001	2.5	5
1001	.	5
1001	.	5
1001	.	5
1001	5	7.5
1001	.	7.5
1001	.	7.5
1001	7.5	12.5
1002	.	10
1002	.	10
1002	.	10
1002	10	12.5
1002	.	12.5
1002	.	12.5
1002	12.5	7.5
1003	.	10
1003	.	10
1003	.	10
1003	10	5
1003	.	5
1003	.	5

[HB]

Is there an ordering variable we don't see? Date of observation or sequential observation number?

[Sri_devi]

Hello, yes we subject seq & dose date which i have mentioned in below tables :

table 1:
SubjectID	subject seq	dose date	Dose	New dose
1001	1	15/07/2021	.
1001	1	16/07/2021	.
1001	1	17/07/2021	.
1001	1	18/07/2021	2.5	5
1001	1	19/07/2021	.
1001	1	20/07/2021	.
1001	1	21/07/2021	.
1001	1	22/07/2021	5	7.5
1001	1	23/07/2021	.
1001	1	24/07/2021	.
1001	1	25/07/2021	7.5	12.5
1002	2	26/07/2021	.
1002	2	27/07/2021	.
1002	2	28/07/2021	.
1002	2	29/07/2021	10	12.5
1002	2	30/07/2021	.
1002	2	31/07/2021	.
1002	2	1/08/2021	12.5	7.5
1003	3	2/08/2021	.
1003	3	3/08/2021	.
1003	3	4/08/2021	.
1003	3	5/08/2021	10	5
1003	3	6/08/2021	.
1003	3	7/08/2021	.

i want this output:

table 2:
SubjectID	subject seq	dose date	Dose	New dose
1001	1	15/07/2021	.	2.5
1001	1	16/07/2021	.	2.5
1001	1	17/07/2021	.	2.5
1001	1	18/07/2021	2.5	5
1001	1	19/07/2021	.	5
1001	1	20/07/2021	.	5
1001	1	21/07/2021	.	5
1001	1	22/07/2021	5	7.5
1001	1	23/07/2021	.	7.5
1001	1	24/07/2021	.	7.5
1001	1	25/07/2021	7.5	12.5
1002	2	26/07/2021	.	10
1002	2	27/07/2021	.	10
1002	2	28/07/2021	.	10
1002	2	29/07/2021	10	12.5
1002	2	30/07/2021	.	12.5
1002	2	31/07/2021	.	12.5
1002	2	1/08/2021	12.5	7.5
1003	3	2/08/2021	.	10
1003	3	3/08/2021	.	10
1003	3	4/08/2021	.	10
1003	3	5/08/2021	10	5
1003	3	6/08/2021	.	5
1003	3	7/08/2021	.	5

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input SubjectID	Dose 	New_dose ;
cards;
1001	.	 
1001	.	 
1001	.	 
1001	2.5	5
1001	.	 
1001	.	 
1001	.	 
1001	5	7.5
1001	.	 
1001	.	 
1001	7.5	12.5
1002	.	 
1002	.	 
1002	.	 
1002	10	12.5
1002	.	 
1002	.	 
1002	12.5	7.5
1003	.	 
1003	.	 
1003	.	 
1003	10	5
1003	.	 
1003	.	 
;

data want;
if 0 then set have;
 do until(last.SubjectID or not missing(New_dose));
   set have;
   by SubjectID;
   retain _Dose _New_dose _lag_New_dose;
   if first.SubjectID then first=1;
   if not missing(Dose) then _Dose=Dose;
   if not missing(New_dose) then _New_dose=New_dose;
 end;

  do until(last.SubjectID or not missing(New_dose));
   set have;
   by SubjectID;
   if missing(New_dose) then do;
     if first then New_dose2=_Dose;
	  else New_dose2=_lag_New_dose;
   end;
   else New_dose2=_New_dose;
   output;
 end;
 _lag_New_dose=_New_dose;
 drop first _:;
run;

[Sri_devi]

hello,

thank you for your help. but i have a question if i have still additional subjects with no dose & New_dose then i want the new_dose2 as 0 instead of lag value of previous subject data.

ex:

data have;
infile cards expandtabs truncover;
input SubjectID Dose New_dose ;
cards;
1001 .
1001 .
1001 .
1001 2.5 5
1001 .
1001 .
1001 .
1001 5 7.5
1001 .
1001 .
1001 7.5 12.5
1002 .
1002 .
1002 .
1002 10 12.5
1002 .
1002 .
1002 12.5 7.5
1003 .
1003 .
1003 .
1003 10 5
1003 .
1003 .
1004 .
1004 .
1004 .
1004 .
1004 .
1005 .
1005 .
1005 .
1005 .
1005 .
;

can you please help me with this.

thanks!

[Ksharp]

OK. Change a little in code for this .

data have;
infile cards expandtabs truncover;
input SubjectID	Dose 	New_dose ;
cards;
1001 .
1001 .
1001 .
1001 2.5 5
1001 .
1001 .
1001 .
1001 5 7.5
1001 .
1001 .
1001 7.5 12.5
1002 .
1002 .
1002 .
1002 10 12.5
1002 .
1002 .
1002 12.5 7.5
1003 .
1003 .
1003 .
1003 10 5
1003 .
1003 .
1004 .
1004 .
1004 .
1004 .
1004 .
1005 .
1005 .
1005 .
1005 .
1005 .
;

data want;
if 0 then set have;
 do until(last.SubjectID or not missing(New_dose));
   set have;
   by SubjectID;
   retain _Dose _New_dose _lag_New_dose;
   if first.SubjectID then do;
        first=1;_Dose=0;_New_dose=0;_lag_New_dose=0;
   end;
   if not missing(Dose) then _Dose=Dose;
   if not missing(New_dose) then _New_dose=New_dose;
 end;

  do until(last.SubjectID or not missing(New_dose));
   set have;
   by SubjectID;
   if missing(New_dose) then do;
     if first then New_dose2=_Dose;
	  else New_dose2=_lag_New_dose;
   end;
   else New_dose2=_New_dose;
   output;
 end;
 _lag_New_dose=_New_dose;
 drop first _:;
run;

[Ksharp]

Or you could try this one .

data have;
infile cards expandtabs truncover;
input SubjectID	Dose 	New_dose ;
cards;
1001 .
1001 .
1001 .
1001 2.5 5
1001 .
1001 .
1001 .
1001 5 7.5
1001 .
1001 .
1001 7.5 12.5
1002 .
1002 .
1002 .
1002 10 12.5
1002 .
1002 .
1002 12.5 7.5
1003 .
1003 .
1003 .
1003 10 5
1003 .
1003 .
1004 .
1004 .
1004 .
1004 .
1004 .
1005 .
1005 .
1005 .
1005 .
1005 .
;
data have1;
 set have(keep=SubjectID Dose rename=(Dose=_Dose));
 by SubjectID;
 if first.SubjectID then n=0;
 if not missing(_Dose) then n+1;
 if not missing(_Dose) and n=1;
 drop n;
run;

data have2;
 update have(keep=SubjectID New_dose obs=0) have(keep=SubjectID New_dose);
 by SubjectID;
 output;
 rename New_dose=New_dose2;
run;

data want;
 merge have have1 have2;
 by SubjectID;
New_dose2=coalesce(New_dose2,_Dose,0);
drop _Dose;
run;

[Sri_devi]

Thank you for your help.
can you please help me to convert the subjectID - has data value - character format - $6 and informat $6 and lenght 6 to numeric format
subjectID
01-001
01-002
01-003

thanks!

[Tom]

---

@Sri_devi wrote:
Thank you for your help.
can you please help me to convert the subjectID - has data value - character format - $6 and informat $6 and lenght 6 to numeric format
subjectID
01-001
01-002
01-003

thanks!

---

This looks like a new question, probably better to start a new thread for it.

What numbers should those strings become?  You have to define the rules before you can program them.

Also why would you want to convert and ID variable into a number.  You shouldn't by doing any arithmetic with ID variable values.

[Ksharp]

As Tom said ,start a brand new session, and post the output you would like to see .

proc format;
picture fmt
 low-high='99-999';
run;

data have;
input subjectID $;
num_subjid=input(compress(subjectID,,'kd'),best32.);
format num_subjid fmt.;
cards;
01-001
01-002
01-003
;

[Sri_devi]

Thank you for your help.