topic Re: Creating a new variable ID in SAS Data Management

Creating a new variable ID

Giovani — Fri, 23 Feb 2018 19:31:01 GMT

Hello SAS Experts,

I want to create a new ID sequence to help to identify a current user for when more than one information is introduced for the same "ID_Number".

Bellow are 3 tables and the different information can be found in the column "RealState".

The DataSample1 is the original. In that, the "ID_Number" 5678 has two different entries for "RealState". I would like to manipulate this and have a new information composed by letter helping to differentiate.

The DataSample2 and DataSample3 are what I am looking for. Can be one or other.

In DataSample2 a letter is added to the "ID_NUmber".

In DataSample3 a new column call "sequence" is created with a letter.

In both situation when more than one observation in "RealState" is entered for the same "ID_Number" different letters was applied.

DATA DataSample1;

InPUT ID_Number $ State $ Age RealState $;

DATALINES;

4456 NC 55 House

5678 SC 43 House

5678 SC 43 Building

4321 CA 67 Farm

9876 FL 43 Gym

;

DATA DataSample2;

InPUT ID_Number $ State $ Age RealState $;

DATALINES;

4456A NC 55 House

5678A SC 43 House

5678B SC 43 Building

4321A CA 67 Farm

9876A FL 43 Gym

;

DATA DataSample3;

InPUT ID_Number $ State $ Age RealState $ Sequence $;

DATALINES;

4456 NC 55 House A

5678 SC 43 House A

5678 SC 43 Building B

4321 CA 67 Farm A

9876 FL 43 Gym A

;

Re: Creating a new variable ID

Reeza — Fri, 23 Feb 2018 19:55:55 GMT

Will the letter always be the last character? If so, use SUBSTR() to extract it and then use CATX() to append it to the other variable.

This assumes you're using Base SAS. If you're using Data Management Studio or Data Flux, then it's likely different.

And why is BUILDING B? Where does the B come from?

If you want numbers instead of letters, consider this approach:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

You can convert the numbers to letters using BYTE() function. 65 to 90 are capital letters corresponding to capital A to Z.

@Giovani wrote:

Hello SAS Experts,

I want to create a new ID sequence to help to identify a current user for when more than one information is introduced for the same "ID_Number".

Bellow are 3 tables and the different information can be found in the column "RealState".

The DataSample1 is the original. In that, the "ID_Number" 5678 has two different entries for "RealState". I would like to manipulate this and have a new information composed by letter helping to differentiate.

The DataSample2 and DataSample3 are what I am looking for. Can be one or other.

In DataSample2 a letter is added to the "ID_NUmber".

In DataSample3 a new column call "sequence" is created with a letter.

In both situation when more than one observation in "RealState" is entered for the same "ID_Number" different letters was applied.

DATA DataSample1;

InPUT ID_Number $ State $ Age RealState $;

DATALINES;

4456 NC 55 House

5678 SC 43 House

5678 SC 43 Building

4321 CA 67 Farm

9876 FL 43 Gym

;

DATA DataSample2;

InPUT ID_Number $ State $ Age RealState $;

DATALINES;

4456A NC 55 House

5678A SC 43 House

5678B SC 43 Building

4321A CA 67 Farm

9876A FL 43 Gym

;

DATA DataSample3;

InPUT ID_Number $ State $ Age RealState $ Sequence $;

DATALINES;

4456 NC 55 House A

5678 SC 43 House A

5678 SC 43 Building B

4321 CA 67 Farm A

9876 FL 43 Gym A

;

Re: Creating a new variable ID

Giovani — Fri, 23 Feb 2018 20:44:17 GMT

Hi Reeza,

Regarding your question:

And why is BUILDING B? Where does the B come from?

B is the new parameter that I am looking to create as the second "ID" to differentiate the 2 occurrences for "ID_Number" 5678.

I imagine doing this either by including in the letter together with the "ID_Number" (like simulation in DataSample2) or creating a new column(like in DataSample3).

Re: Creating a new variable ID

Reeza — Fri, 23 Feb 2018 20:47:15 GMT

Ah, well then the BYTE and enumeration approach I suggested will work.

@Giovani wrote:

Hi Reeza,

Regarding your question:

And why is BUILDING B? Where does the B come from?

B is the new parameter that I am looking to create as the second "ID" to differentiate the 2 occurrences for "ID_Number" 5678.

I imagine doing this either by including in the letter together with the "ID_Number" (like simulation in DataSample2) or creating a new column(like in DataSample3).

Re: Creating a new variable ID

Giovani — Fri, 23 Feb 2018 20:55:30 GMT

Ok.

Could you please help me to build that code?

Re: Creating a new variable ID

Reeza — Fri, 23 Feb 2018 21:01:38 GMT

Did you look at the link I included in my first answer? That's about 90% of the solution you want.

@Giovani wrote:

Ok.

Could you please help me to build that code?

Re: Creating a new variable ID

Giovani — Fri, 23 Feb 2018 22:56:12 GMT

Reeza,

Thank very much for the help!

I didn`t get the byte code works, but I made a If then and that worked after the initial codes provided by the site.

Re: Creating a new variable ID

Reeza — Sat, 24 Feb 2018 04:42:29 GMT

letter = byte(64 + your_number);