- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
is there any ID variable linking these datasets? and do you want the variables to be char or numeric?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
do you want decimals or not? because your desired output you added some decimals to var2 from set2 that weren't there before.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content