Solved
Contributor
Posts: 60

# =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

Hi

Looking to perform a similar function in SAS to =Left(x,4)  on excel.

Does anyone know the command for this?

Accepted Solutions
Solution
‎10-03-2017 07:48 AM
Super User
Posts: 8,075

## Re: =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

If you really have these variables as character strings then SUBSTR(x,1,4) will take the first 4 characters of the field.  If for some reason the value is pushed right in the field you could use the LEFT() or STRIP() function to remove the leading spaces first.

More likely you have the data stored as a number.  If it is really a date in the format YYYYMMDD then you should store it as a date.  Then the YEAR() function will be able to take the year part of the date for you.

If instead you just have it stored as number (For example 19,701,231) then you could use INT(datadate/10000) to find the digits from the 10 million to 10 thousands place.  Or you could convert it to a date and then use the YEAR function.  year(input(put(datadate,z8.),yymmdd8.))

All Replies
Super Contributor
Posts: 350

## Re: =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

Hi,

You can try using Substr function or directly you can use year function to extract the year from the given date.

Thanks,

Shiva

Solution
‎10-03-2017 07:48 AM
Super User
Posts: 8,075

## Re: =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

If you really have these variables as character strings then SUBSTR(x,1,4) will take the first 4 characters of the field.  If for some reason the value is pushed right in the field you could use the LEFT() or STRIP() function to remove the leading spaces first.

More likely you have the data stored as a number.  If it is really a date in the format YYYYMMDD then you should store it as a date.  Then the YEAR() function will be able to take the year part of the date for you.

If instead you just have it stored as number (For example 19,701,231) then you could use INT(datadate/10000) to find the digits from the 10 million to 10 thousands place.  Or you could convert it to a date and then use the YEAR function.  year(input(put(datadate,z8.),yymmdd8.))

Occasional Contributor
Posts: 5

## Re: =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

This is exactly what I needed!  Thanks!

For those using Miner, I have an account number 15 digits long (Char15). I only need the first 11.

data &EM_EXPORT_TRAIN; set &EM_IMPORT_DATA;

CM11=SUBSTR(ACCT_NO,1,11);

run;

Returns the same value as Excel; =

Left(ACCT_NO,11)

Super User
Posts: 6,754

## Re: =LEFT(X,4) EXCEL STYLE FUNCTION ON SAS

If DATADATE is character, and left-hand-justified, here are a couple of simple tricks to getting the year.

If you want YEAR as numeric, read just the first four characters of DATADATE:

If you want YEAR as character, copy into a shorter variable:

length year \$ 4;

If DATADATE is numeric, then other posters have mentioned the right issues.  You have to begin with asking what number is actually stored there (the digits in your post, vs. a SAS date).

Good luck.

🔒 This topic is solved and locked.