BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi mates!
I came across with this unexpected fact that caused some serious consequences.

So, I have a table1:

phone_num mob_num
'1' 1
'2' 2
'3' 3
...

phone_num has the same value as mob_num. The only difference is that phone_num is a text variable, mob_num is a numeric one.
Also I have a table2:
phone_num
'4'
'5'
'6'
...

My simplest code is:

data table3;
set table1 table2;
if mob_num = . then mob_num=phone_num;
run;

Question: what should be the result table?


I expected to see table3:
phone_num mob_num
'1' 1
'2' 2
'3' 3
'4' 4
'5' 5
'6' 6

However, in fact the result appears like this:
phone_num mob_num
'1' 1
'2' 2
'3' 3
'4' 4
'5' 4
'6' 4

What am I doing wrong in this simplest code?
I assume that I shouldn't see the expected result, but why there was not any warnings in the log?
Peace!

Victor Popovich
MTS, Russia Message was edited by: Victor_Popovich
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you use PROC CONTENTS to analyze the SAS variable types, which are either CHARACTER or NUMERIC. Unless there are user formats being applied, your assignment statement appears to be attempting to relate different variable types -- I would expect some diagnostic messages in your SAS log would be generated and would help you self-diagnose the program and your data.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
There aren't any specific formats used. Phone_num has $11. and mob_num has best12. format.

I also expected to see something in log, but I didn't. The only statement in log was that some character variable were converted to numeric ones;

Is is also important that if I go this way:

data table3;
set table1 table2;
run;

data table3;
set table3;
if mob_num = . then mob_num=phone_num;
run;

I will see exactly what I needed!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
For consideration, the values are not equal. You will need to consider extracting the numeric portion of the character-type variable, using SAS DATA step functions like SCAN, SUBSTR, STRIP, and then use the INPUT function to convert the character result to numeric. Then you can effectively compare values.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
It is not about how to compare variables, it is about why '4' is placed to the every next observation?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Okay - sounds like you want to do a MERGE with a BY statement instead of a SET.

Scott Barry
SBBWorks, Inc.
Doc_Duke
Rhodochrosite | Level 12
Victor,

The behavior you saw has to do with the way SAS retains values in a SET with different variables in the two datasets. I've seen it documented (long ago), but couldn't find it in a cursory search of the 9.2 online docs. If you add the one line with the comment to table 2, you get the expected results.

Doc Muhlbaier
Duke
--------------------
DATA table1;
LENGTH Phone_Num $10 Mob_Num 8;
input phone_num mob_num;
cards;
1 1
2 2
3 3
RUN;

DATA table2;
LENGTH Phone_Num $10;
INPUT phone_num;
mob_num=.; * <--- add this statement to get desired behavior;
cards;
4
5
6
RUN;

data table3;
set table1 table2;
if mob_num = . then mob_num=phone_num;
run;

PROC PRINT; RUN; You also get the desired result if you sort table1 and table2 by Phone_num and add a BY Phone_Num; statement to the third data step;


Message was edited by: Doc@Duke
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Very useful DOC reference below:

http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001292604.htm

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument this topic/post:

data step processing missing values site:sas.com
Doc_Duke
Rhodochrosite | Level 12
Thanks, Scott. Here is the line I was looking for.

"When Reading a SAS Data Set

When variables are read with a SET, MERGE, or UPDATE statement, SAS sets the values to missing only before the first iteration of the DATA step. (If you use a BY statement, the variable values are also set to missing when the BY group changes.) The variables retain their values until new values become available;..."

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1030 views
  • 0 likes
  • 3 in conversation