topic Proc format text to a number in Microsoft Integration with SAS

Proc format text to a number

DME790 — Thu, 23 Nov 2017 23:13:45 GMT

Hi All,

I need to format text to a number - I'm trying to use Proc Format

Proc Format lib=work;
	Value SPGroup
	SP2018-01 = '1'
	SP2018-02 = '2'
	SP2018-03 = '3'
	SP2018-04 = '4'
	SP2018-05 = '5'
	SP2018-06 = '6'
	SP2018-07 = '7'
	SP2018-08 = '8'
	SP2018-09 = '9'
	SP2018-10 = '10'
	SP2018-11 = '11'
	SP2018-12 = '12'
	SP2018-13 = '13'	
;

RUN;

is it possible to use proc format in this instance or is there an easier way to just return the last two characters of the observation

ie return 01 from SP2018-01.

Any help is appreciated.

Cheers

Dean

Re: Proc format text to a number

art297 — Thu, 23 Nov 2017 23:28:33 GMT

Here is one alternative:

data have;
  input SPGroup $9.;
  cards;
SP2018-01
SP2018-02
SP2018-03
SP2018-04
SP2018-05
SP2018-06
SP2018-07
SP2018-08
SP2018-09
SP2018-10
SP2018-11
SP2018-12
SP2018-13	
;

data want;
  set have;
  wantnum=input(substr(SPGroup,index(SPGroup,'-')+1),8.);
run;

Art, CEO, AnalystFinder.com

Re: Proc format text to a number

Reeza — Thu, 23 Nov 2017 23:39:02 GMT

SCAN()

 num = scan("SP2018-01", 2, "-");

Re: Proc format text to a number

DME790 — Fri, 24 Nov 2017 03:24:17 GMT

Thanks @Reeza and @art297 for the response.

I ended up going with

SP = SUBSTR(TRIM(SETTLEMENT_PERIOD_NM),length(Trim(SETTLEMENT_PERIOD_NM))-1);

Cheers

Dean

Re: Proc format text to a number

Tom — Fri, 24 Nov 2017 15:58:34 GMT

Your examples use a delimiter so it is each to use SCAN(). Use -1 to pull off the last term.

char=scan('SP2018-01',-1,'-');

If you just want the last two characters then use SUBSTRN().

str='SP2018-01              '; 
char=substrn(str,length(str)-1);

If you want generate a number then use the INPUT() function. If you want to go the PROC FORMAT route then use a INFORMAT instead of a FORMAT if you want to generate a number. You could also look into using the REGEXPE option on your format definition.

proc format ;
invalue spgrp 
  's/SP\d\d\d\d-(.*)/$1/' (regexpe) = _same_ 
  other = _error_
;
run;

data test;
   input @1 sptext $10.  @1 spgrp spgrp. ;
cards;
SP2018-01
SP2017-02
XXXX
;

proc print;
run;