topic Re: How to clean data to be able to join through it? in SAS Programming

How to clean data to be able to join through it?

GN0001 — Wed, 19 Jan 2022 21:16:48 GMT

Hello team,

I have two datasets. One has a variable with character datatype as d123.22, the second dataset has a variable with character datatype as d12322. I need to make them consistent to be able to join them through this field. How can I reach there?

Regards,

BlueBlue

Re: How to clean data to be able to join through it?

Reeza — Wed, 19 Jan 2022 21:20:37 GMT

Clarify your question first.

By 'datatype' do you mean that's an example of a value stored in the variable?

What EXACTLY are the proc contents output in terms of format and type.

@GN0001 wrote:

Hello team,

I have two datasets. One has a variable with character datatype as d123.22, the second dataset has a variable with character datatype as d12322. I need to make them consistent to be able to join them through this field. How can I reach there?

Regards,

BlueBlue

Re: How to clean data to be able to join through it?

GN0001 — Wed, 19 Jan 2022 21:29:09 GMT

Hello team,

Please disregard datatype.

We have two data types in SAS: character and number

Here what I need is to make these two look same:

v123.12 and v12312

I need the second one look like first one.

Respectfully,

BlueBlue

Re: How to clean data to be able to join through it?

Reeza — Wed, 19 Jan 2022 21:54:32 GMT

Both of those must be character if the V is in there. Please confirm this is correct.

@GN0001 wrote:

Hello team,

Please disregard datatype.

We have two data types in SAS: character and number

Here what I need is to make these two look same:

v123.12 and v12312

I need the second one look like first one.

Respectfully,

BlueBlue

Re: How to clean data to be able to join through it?

GN0001 — Wed, 19 Jan 2022 22:36:05 GMT

Hello Reza,

Thanks for your response!

I have no idea what "." in compress(field,' . '); do.

I know only compress function remove between leading ending spaces.

Also, I am curious as why you have used: upcase.

Regards,

blueblue

Re: How to clean data to be able to join through it?

Reeza — Wed, 19 Jan 2022 22:56:20 GMT

@GN0001 wrote:

I know only compress function remove between leading ending spaces.

COMPRESS Function

Returns a character string with specified characters removed from the original string.

Please read the documentation first.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/n0fcshr0ir3h73n1b845c4aq58hz.htm

COMPRESS(source ,< characters> ,< modifier(s)>)

characters

specifies a character constant, variable, or expression that initializes a list of characters.

By default, the characters in this list are removed from the source argument. If you specify the K modifier in the third argument, only the characters in this list are kept in the result.

@GN0001 wrote:

Hello Reza,

Thanks for your response!

I have no idea what "." in compress(field,' . '); do.

I know only compress function remove between leading ending spaces.

Also, I am curious as why you have used: upcase.

Regards,

blueblue

Re: How to clean data to be able to join through it?

GN0001 — Wed, 19 Jan 2022 23:28:06 GMT

Hello Reza,

I can't understand this part:

By default, the characters in this list are removed from the source argument.

----------------------

Regarding this example:

data _null_;

x='123–4567–8901 B 234–5678–9012 c';

y=compress(x, 'ABCD', 'l'); put y=; run;

What does 'ABCD' do? What does 'I' do?

Regards,

Blue Blue

Re: How to clean data to be able to join through it?

Reeza — Wed, 19 Jan 2022 23:41:30 GMT

Post the output as well.

Re: How to clean data to be able to join through it?

Reeza — Wed, 19 Jan 2022 23:42:01 GMT

Is English your second language? If so, what is your first? The documentation is available in more than one language.

Re: How to clean data to be able to join through it?

GN0001 — Thu, 20 Jan 2022 00:31:11 GMT

Hello Reza,

Believe me I couldn't understand it first. Now, I understand it. I think that I was not able to understand it in my first language (Turkish).

I read documentation several times. I couldn't understand what it says until I came to the forum.

Is it ok to ask forum members what our first language is in this forum?

I have to understand it in English to be able to express it in English to my boss.

Thanks

BlueBlue

Re: How to clean data to be able to join through it?

Reeza — Thu, 20 Jan 2022 02:50:37 GMT

@GN0001 wrote:

Is it ok to ask forum members what our first language is in this forum?

I have to understand it in English to be able to express it in English to my boss.

Thanks

BlueBlue

I'm not aware of any rules that say it's not ok to ask if English is the first language. As you've probably noticed, there are several languages on here, it's not solely an English forum.

https://communities.sas.com/t5/SAS-Tech-Tip/SAS-활용-노하우-MONOTONIC-function-in-PROC-SQL/ta-p/781620

Re: How to clean data to be able to join through it?

Tom — Thu, 20 Jan 2022 02:59:27 GMT

The point was to see if there was a better way for you understand since SAS does publish documentation in multiple languages.

Reading about computer languages in any human language is difficult. Sometimes the words used in computer programming look like words in English (or some other language) but they actual have specific specialized meaning in computer programming in general and in SAS syntax in particular.

Plus when reading the documentation it is important to make the connection between the name the documentation uses for an argument to a function (the arguments to a function are the inputs to the function) because in the explanation they will reference those argument names. So yet another layer of meaning on common words that gives them special meaning within the context of reading the documentation.

For example in the COMPRESS() function documentation they use the words SOURCE, CHARACTERS and MODIFIERS to be the names of the three arguments to the function.

Syntax

COMPRESS( source <, characters> <, modifier(s)>)

Re: How to clean data to be able to join through it?

Kurt_Bremser — Thu, 20 Jan 2022 10:06:35 GMT

Let's face it: English is the lingua franca of computing. Developers (especially across different native languages) use English as their common idiom. All (almost, I am talking about those used world-wide) programming languages are based on English (ALGOL, COBOL, FORTRAN, BASIC, PASCAL, C, PL/1, SQL, VBA, Python, Go, Rust, .....).

Documentation is written in English first, translations are added as an afterthought, and sometimes the translation makes it even harder to understand or ends up being plainly wrong (that's why I also prefer to read the English manuals first for devices I buy, because they are often better and more accurate than the German ones).

Therefore, to become versed in "computer English" is a must for every aspiring computer user who wants to go beyond the pointy-clicky helpers that are designed to take language mostly out of the process. Acquiring a sense for everyday English on the side is a benefit that helps in getting around the world. I have yet to find a place where English would not at least help in getting along.

Re: How to clean data to be able to join through it?

GN0001 — Sat, 22 Jan 2022 03:16:12 GMT

Hello Reza,
Thanks for ongoing support.

Many things are not rules but we know that if we bring them up, it hurts.
Often the times, I look for something and I can’t understand it from documentations because I am at work and deadlines are getting approached, I am under pressure and finally it is hard to understand!!!!

Respectfully,
Guity

Re: How to clean data to be able to join through it?

GN0001 — Fri, 28 Jan 2022 00:20:25 GMT

Hello,
I do whatever I can't remove dot. I looked into the code that you sent
code2 = compress(code, '.');
run;
the dot is till in the string.
This is what I have G34.z or z123.22.
Regards,
Blue & Blue

Re: How to clean data to be able to join through it?

Kurt_Bremser — Fri, 28 Jan 2022 07:40:19 GMT

Post the complete log of the whole step by copy/pasting it into a windowopened with this button:

Re: How to clean data to be able to join through it?

GN0001 — Fri, 28 Jan 2022 21:14:10 GMT

Hello Reza,
The code posted here helped me to solve my issue,
I am grateful to you for sharing the code.
Regards,
blue & blue