topic Re: grouping on substring in SAS Enterprise Guide

grouping on substring

jackthecat — Fri, 02 Jun 2017 10:14:59 GMT

Hi all, my second question...

I use the query wizard to query data rather then command line type programming.

I'm afraid I don't know what SAS EG version it is... it's not on my PC, it is a remote instance that I access via an online method. It is quite a restrictive set up, but it's the only way I can access this data.

One of the fields I want to include in my query is populated with a 5 char string (for example AA123, AA124, AA125 etc). However, if I pull it in as is, the query result is way too big, and all I actually need is the first two characters of the code.

In sql it would be

select substring(Hcode,1,2) as H_summary... if that clarifies?

I can't work out how to do it though.

Any help gratefully received.

Re: grouping on substring

BrunoMueller — Fri, 02 Jun 2017 10:32:55 GMT

Just use the query builder in SAS Enterprise Guide and add a computed column like below, then you can add other columns and use summarization functions on it.

The expression for the new column would be SUBSTR(yourColumn, start, length)

Re: grouping on substring

jackthecat — Fri, 02 Jun 2017 10:41:49 GMT

perfect, thanks!