SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a new variable ID

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Creating a new variable ID

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

;


Accepted Solutions
Solution
‎02-23-2018 05:56 PM
Super User
Posts: 23,365

Re: Creating a new variable ID

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

;


 

View solution in original post


All Replies
Solution
‎02-23-2018 05:56 PM
Super User
Posts: 23,365

Re: Creating a new variable ID

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

;


 

Contributor
Posts: 35

Re: Creating a new variable ID

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).

Super User
Posts: 23,365

Re: Creating a new variable ID

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).


 

Contributor
Posts: 35

Re: Creating a new variable ID

Ok. 

Could you please help me to build that code?

Super User
Posts: 23,365

Re: Creating a new variable ID

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?


 

Contributor
Posts: 35

Re: Creating a new variable ID

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. 

Super User
Posts: 23,365

Re: Creating a new variable ID

letter = byte(64 + your_number);
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 232 views
  • 0 likes
  • 2 in conversation