topic Re: how to get the data value in one column to other column logically in New SAS User

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

Sri_devi — Fri, 03 Jun 2022 11:39:10 GMT

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

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

HB — Fri, 03 Jun 2022 16:35:10 GMT

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

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

Sri_devi — Sat, 04 Jun 2022 02:23:34 GMT

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

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

Ksharp — Sat, 04 Jun 2022 04:04:29 GMT

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;

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

Sri_devi — Sat, 04 Jun 2022 14:35:11 GMT

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!

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

Ksharp — Sun, 05 Jun 2022 05:13:20 GMT

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;

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

Ksharp — Sun, 05 Jun 2022 05:37:28 GMT

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;

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

Sri_devi — Sun, 05 Jun 2022 11:47:11 GMT

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!

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

Tom — Sun, 05 Jun 2022 14:47:04 GMT

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

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

Ksharp — Mon, 06 Jun 2022 11:51:41 GMT

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
;

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

Sri_devi — Thu, 09 Jun 2022 03:43:05 GMT

Thank you for your help.