joins -- code does not match exactly 1

[DonaZ]

I cannot join the two tables because of the following problem:

Table 1 Table 2
Code Code
001 1
002 2
048 48
678 678


How do I remove the zero placeholders in Table 1?
Any suggestions

Thank you for your time.

 

[ChrisW75]

Two ways to do this.

1 - convert code to a numeric variable on both tables.

  data table1b;
    set table1;
    code_num = input(code,3.);
  run;
  ...
  ...

2 - pad out the variable on the second table with leading zeroes.

  data table2b;
    set table2;
    code = put(input(code,3.),z3.);
  run;

I would generally favour the second method as character variables are more reliable for joining (you don't have to worry about numeric accuracy and rounding errors).

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

Hi Chris,

I used this code and received the following error. What does this mean?

data table2b;
set table2;
code = put(input(code,3.),z3.);
run;


ERROR 85-322: Expecting a format name.

ERROR 76-322: Syntax error, statement will be ignored.

Thank you for your time.

 

[ChrisW75]

Well this:-

data _null_;

    code1 = '33';
    
    code = put(input(code1,3.),z3.);

    put code=;
run;

works fine when I run it. Is "code" a character or numeric field? I assumed it was a character field because it was left aligned in your example. Try submitting the above code on it's own just to confirm that you have access to the formats.
Could you copy and paste the log for your error exactly as it appears in the log file? The error message should put underscores under the point where the error occurs.
If you wrap the log in code markers (put "code" in square brackets (no quotes) before the log, and "/code" in square brackets after the log to make it easier to read).
This is what I got when I ran it, both as a numeric and as a character field:-

23   data _null_;
24
25       code1 = '33';
26
27       code = put(input(code1,3.),z3.);
28
29       put code=;
30   run;

code=033
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


31   data _null_;
32
33       code1 = 33;
34
35       code = put(input(code1,3.),z3.);
36
37       put code=;
38   run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      35:22
code=.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

So I still didn't get the error message, just a note and an incorrect result...

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

Chris,
Thank you very much.
Both queries worked today. My original field code had another name so I changed the field to code. I'm not sure what I did wrong.
The first one had error messages, but the data was changed.

The second query with the data_null_; worked.

I very new to SAS and like to have a better understanding of why you used what you used. If you have time, can you explain to me each line? Why did you use "data_null_"? Why did you use "33"? etc.

Thank you again.

Dona

 

[ChrisW75]

Sure.
_null_ is a SAS reserved dataset name. It's not really a dataset at all, it allows you to run a regular datastep without keeping an output dataset. I use it for tests like this because I'm not keeping a result, just testing some code and writing out to the log window. This is called a "Null Datastep". They are commonly used in conjunction with a FILE statement to write out data to a text file as well.
Putting the 33 in quotes (either single or double) tells SAS (and pretty much any other programming language) that it is a text string rather than a numeric. Without the quotes it is a numeric. In SAS you don't need to pre-define the types of your variables, so if you're creating a new one like that you need to be careful how you pass the data to it.

In my first step, because CODE is a character field, and I'm wanting to put a numeric format (z3.) onto it, I need to first convert it to a numeric field. To do that I use the INPUT function to input the character string into a numeric variable using the 3. format. Then use the PUT function to convert it back to a text string using the z3. format.
If CODE is a numeric field to start with, this won't work (as demonstrated in the second step), as the INPUT part will fail, simply cut out the INPUT function and then it'll work.
I hope that helps explain it all...
Chris.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[DonaZ]

Thank you Chris!