New SAS User

Completely new to SAS or trying something new with SAS? Post here for help getting started.
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Lapis Lazuli | Level 10

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
1 ACCEPTED SOLUTION

Accepted Solutions
tarheel13
Rhodochrosite | Level 12

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;

View solution in original post

9 REPLIES 9
tarheel13
Rhodochrosite | Level 12

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

NewUsrStat
Lapis Lazuli | Level 10
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.
tarheel13
Rhodochrosite | Level 12

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

tarheel13
Rhodochrosite | Level 12

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;
NewUsrStat
Lapis Lazuli | Level 10
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.
Tom
Super User Tom
Super User

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.

NewUsrStat
Lapis Lazuli | Level 10
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.
Tom
Super User Tom
Super User

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

 

 

NewUsrStat
Lapis Lazuli | Level 10
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!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1882 views
  • 3 likes
  • 3 in conversation