DATA Step, Macro, Functions and more

What am I doing wrong in this simplest code?

Reply
N/A
Posts: 0

What am I doing wrong in this simplest code?

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
Super Contributor
Super Contributor
Posts: 3,174

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
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!
Super Contributor
Super Contributor
Posts: 3,174

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
It is not about how to compare variables, it is about why '4' is placed to the every next observation?
Super Contributor
Super Contributor
Posts: 3,174

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
Okay - sounds like you want to do a MERGE with a BY statement instead of a SET.

Scott Barry
SBBWorks, Inc.
Trusted Advisor
Posts: 2,116

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
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
Super Contributor
Super Contributor
Posts: 3,174

Re: What am I doing wrong in this simplest code?

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
Trusted Advisor
Posts: 2,116

Re: What am I doing wrong in this simplest code?

Posted in reply to deleted_user
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;..."
Ask a Question
Discussion stats
  • 8 replies
  • 274 views
  • 0 likes
  • 3 in conversation