BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giovani
Obsidian | Level 7

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

;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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

;


 

Giovani
Obsidian | Level 7

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

Reeza
Super User

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


 

Giovani
Obsidian | Level 7

Ok. 

Could you please help me to build that code?

Reeza
Super User

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?


 

Giovani
Obsidian | Level 7

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. 

Reeza
Super User
letter = byte(64 + your_number);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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