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

I have a numeric column "ACCTID" defined as length 16, example value 3508777719876543.  I want to extract the 1st 3 digits from the value and create a new column with it's contents.  I have created a computed column named "ACCTID_3" using the following advanced expression:

(SUBSTR(PUT(t1.ACCTID, BEST.),1,3)) and my result is 3.5  (the result I want is 350) ((I don't care if the result is numeric or character)

In the advanced edit window, on page 2 of 3 titled "Modify Additional Options", the column type is defined as numeric, with 'none' for the format.

I have changed this to column type 'character' and it does not like it.  Can you please tell me what I am doing wrong?  Any help would be greatly appreciated!!

1 ACCEPTED SOLUTION

Accepted Solutions
TriciaA
Calcite | Level 5

Sorry ... I meant force it for the output on the screen where it asked if you want Character or Numeric. 

Select Character and set the format to $8.  Setting the format should be enough.

You don't have to set the length -leave it blank.

Change your original calculation back to this:SUBSTR(PUT(t1.ACCOUNT_ID, 8.),1,3)

View solution in original post

5 REPLIES 5
TriciaA
Calcite | Level 5

Try this instead:

(SUBSTR(PUT(t1.ACCTID, 8.),1,3))

and set the format to $8. or make it a character.  A character value will not insert the decimal point. 

000_EG_get_prefix.png

Tricia

SAS BI Notes

ncsthbell
Quartz | Level 8

I am using SAS EG 4.2, our screens are a bit different so we must not be on the same release.  I am still getting the decimal. No matter what I change the column format to, it makes it numeric. 

On the 2nd screen "Modify additional properties", I give the column & alias name and under 'column type' there are radio buttons for either Character or Numeric.  I am chosing 'character'.   On the 3rd screen, it still interprets it as numeric.  When I hit the 'back' key and return to the 2nd screen, the radio button that I had set to 'character' has gone back to 'numeric. 

Here is the code generated (on 3rd screen)

Alias: TP_NAME_ACCTID_3

Column: TP_NAME_ACCTID_3

Type: Numeric            <=== shows as numeric even when I set the column type to character

Format: None

Length: Default

Summary: None

Expression:

   (SUBSTR(PUT(t1.ACCOUNT_ID, 8.),1,3))

My result is still coming as 3.5

I was going to send screen shots and couldn't figure out how to copy & paste them in the body of this as you did above. 

I can accomplish this in Oracle Sql but I am new to SAS and I just can't get it to work!

TriciaA
Calcite | Level 5

Try setting the format to $8. to force the character. Mine would only do it when I added the format.

ncsthbell
Quartz | Level 8

Changed to format $8

Error generated in SAS EG:

19 (SUBSTR(PUT(t1.ACCOUNT_ID, $8.),1,3)

20 ) LENGTH=3 AS TP_ADDR_ACCTID_3

21 FROM ORACLDS.ADDRESS AS t1

22 WHERE t1.SOURCE_ID = 5 AND t1.HISTORY_FLAG = 'N'

23 ORDER BY t1.ENTITY_ID, t1.ADDRESS_ID;

ERROR: Character format $ in PUT function requires a character argument.

ERROR: Character format $ in PUT function requires a character argument.

TriciaA
Calcite | Level 5

Sorry ... I meant force it for the output on the screen where it asked if you want Character or Numeric. 

Select Character and set the format to $8.  Setting the format should be enough.

You don't have to set the length -leave it blank.

Change your original calculation back to this:SUBSTR(PUT(t1.ACCOUNT_ID, 8.),1,3)

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5819 views
  • 1 like
  • 2 in conversation