topic Replace values in a numeric table based on a character table in New SAS User

Replace values in a numeric table based on a character table

NewUsrStat — Wed, 06 Mar 2024 15:00:25 GMT

Hi guys,

suppose to have the following two datasets:

data set1;
input Variable1 Variable2;
cards;
124.3 342.0
721.30 876.05
. 654.98
. 543.6
456.1 .
39 .
783.01 .
765.10 765.0
;
run;

data set2;
input Variable1 $ Variable2 $;
cards;
1243 3420
72130 87605
V2540 65498
. 543.6
4561 V3461
39 .
78301 .
76510 7650
;
run;

Can you please help me with these two issues?

1) prevent to trim the 0s at the end of numbers in set1 (not to round at the integer when there is 0 as the only one decimal because in my data 342.0 (chosen as an example) is an information while 342 is another one. They are not equivalent and so .0 must be there) ;

2) I need to replace missing values in set1 with values in set2 when they are available in set2. If a value is missing in set2 and missing in set1 no action must be done. The remaining values must be as in set1, i.e., for example 124.3 must remain as is and not transformed as 1243.

My issue is that while set1 is numeric, set2 is character. Maybe set1 could be transformed as character but I need that numbers must remain as they are, i.e., 124.3 and not 1243.

Desired output (in bold replaced values):

Variable1	Variable2
124.3	342.0
721.30	876.05
V2540	654.98
	543.6
456.1	V3461
39
783.01
765.10	765.0

Re: Replace values in a numeric table based on a character table

tarheel13 — Wed, 06 Mar 2024 15:15:52 GMT

is there any ID variable linking these datasets? and do you want the variables to be char or numeric?

Re: Replace values in a numeric table based on a character table

NewUsrStat — Wed, 06 Mar 2024 15:17:54 GMT

Unfortunately there is not an ID variable. I received data as they appear here. I think I can add easily by numbering from 1 to the n rows of the table. I think they can be as character.

Re: Replace values in a numeric table based on a character table

tarheel13 — Wed, 06 Mar 2024 15:27:07 GMT

do you want decimals or not? because your desired output you added some decimals to var2 from set2 that weren't there before.

Re: Replace values in a numeric table based on a character table

tarheel13 — Wed, 06 Mar 2024 15:35:50 GMT

try this

data set1;
input Variable1 Variable2;
cards;
124.3 342.0
721.30 876.05
. 654.98
. 543.6
456.1 .
39 .
783.01 .
765.10 765.0
;
run;

data set1_1;
   set set1;
   id=_n_;
   if variable1 ne . then var1=strip(put(variable1,8.2));
   if variable2 ne . then var2=strip(put(variable2,8.2));
run;
 
data set2;
input Variable1 $ Variable2 $;
id=_n_;
cards;
1243 3420
72130 87605
V2540 65498
. 543.6
4561 V3461
39 .
78301 .
76510 7650
;
run;

proc sql;
   create table set3 as 
   select s1.var1, s1.var2, s2.variable1, s2.variable2
      from set1_1 as s1
      left join set2 as s2
      on s1.id=s2.id;
quit;

data want;
   set set3;
   if missing(var1) and ^missing(variable1) then var1=variable1;
   if missing(var2) and ^missing(variable2) then var2=variable2;
run;

Re: Replace values in a numeric table based on a character table

NewUsrStat — Wed, 06 Mar 2024 16:02:57 GMT

Thank you very much tarheel13 but regarding the use of strip it doesn't work because looking for example at the first number 124.3 it becomes 124.30 that has a different meaning compared with 124.3 that must remain as it is.

Re: Replace values in a numeric table based on a character table

Tom — Wed, 06 Mar 2024 16:17:07 GMT

To prevent the trailing zeros after the decimal place from being removed do NOT convert the strings in numbers. There is no difference between 342 and 342.0 as they both represent the same number as 300+40+2.

Are these ICD9 codes? If so store them as character only.

Re: Replace values in a numeric table based on a character table

NewUsrStat — Wed, 06 Mar 2024 16:20:07 GMT

Wow, you won! Unfortunately yes they are ICD9 (devil). If I store them as character the code you provided me in my previous post doesn't work. It rounds numbers.

Re: Replace values in a numeric table based on a character table

Tom — Wed, 06 Mar 2024 16:21:54 GMT

@NewUsrStat wrote:
Wow, you won! Unfortunately yes they are ICD9 (devil). If I store them as character the code you provided me in my previous post doesn't work. It rounds numbers.

Store the ICD codes without the period. It saves a space.

You can always put it back in if you want it to look pretty in a report.

Re: Replace values in a numeric table based on a character table

NewUsrStat — Wed, 06 Mar 2024 16:23:33 GMT

Ok sounds the best solution also for further computations. I will keep them as they are until the analysis will be finished. Thank you very much!