topic Removing Leading/Trailing blanks using data Step/Proc SQL in SAS Programming

Removing Leading/Trailing blanks using data Step/Proc SQL

RaviSPR — Tue, 04 Jul 2023 19:48:26 GMT

Hi,

I read in the data from CSV file using infile & length statement.

Here, I AM NOT supposed to Alter the below Infile DataStep & Raw Source file.

data bs_raw1(keep=fnd dec_val );
length
/* 01 */ fnd $ 50
/* 02 */ dec_val 8
;
informat fnd $Upcase50. dec_val Comma15.2;
infile Rin1 firstobs=2 dsd truncover termstr=&ftermstr.;

Rin1 File-

2 Digit Fnd	Trade Amount
1L	0.71
1S	2.32
1J	0.79
1T	3.43
1R	1.90
21	4.21
26	2.40

Here in the raw source file, there are Leading/Trailing blanks for some of values. For example, value 1L is -" 1L".

Code I tried-

PROC SQL;

/* SELECT Strip(Left(fnd)) INTO :fnd1 -:fnd&fnd_count. Trimmed FROM BS_RAW1 ; */

select trimn(fnd) into :fnd1 -:fnd&fnd_count. FROM BS_RAW1 ;

/* SELECT prk_fund INTO :fnd1 -:fnd&fnd_count. Trimmed FROM BS_RAW1 ; */

SELECT dec_val FORMAT 18.2 INTO :dec_val1 -:dec_val&fnd_count. FROM BS_RAW1 ;

Quit;

%Put fnd = **&fnd1.**

dec_val = **&dec_val1.** ;

%Put fnd = **&fnd2.**

dec_val = **&dec_val2.** ;

Results-

35 %Put fnd = **&fnd1.**
SYMBOLGEN: Macro variable FND1 resolves to 1L
36 dec_val = **&dec_val1.** ;
SYMBOLGEN: Macro variable DEC_VAL1 resolves to 0.71
fnd = ** 1L ** dec_val = **0.71**
37 %Put fnd = **&fnd2.**
SYMBOLGEN: Macro variable FND2 resolves to 1S
38 dec_val = **&dec_val2.** ;
SYMBOLGEN: Macro variable DEC_VAL2 resolves to 2.32
fnd = **1S** dec_val = **2.32**

I tried all the possibilities. SO, Can anyone please help me to remove the Leading/Trailing blank using either directly in the SAS data set after reading the raw source file or in the Proc SQL step which is creating Fnd macro variables?

I used Trimn, Compress, Left in the NEW Data step after reading the raw source file. But no Luck.

Data BS_RAW1ff;
Set BS_RAW1;
c=Length(fnd);
fnd2 = Trimn(fnd);
c2=Length(fnd2);
fnd3 = CATS('*',fnd);
fnd4 = Compbl(fnd);
fnd5 = Trim(Left(fnd));

Run;

Thanks

RaviSPR

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

Reeza — Tue, 04 Jul 2023 20:02:27 GMT

Print the characters using a hex format to see what those spaces are and explicitly remove them using TRANSLATE() or COMPRESS(). If you only have two digit codes and no spaces you can use COMPRESS() with the s modifier (see the docs).

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

Tom — Tue, 04 Jul 2023 20:19:31 GMT

If LEFT() or STRIP() did not remove the leading white space then it is NOT a space character. It is some other character(s). Such as TAB ('09'x) or non-breaking spaces ('A0'x).

Did you really mean to imply a decimal place when the strings in the CSV file for the variable DEC_VAL did not include one? That is what using the 2 on the end of the INFORMAT is for. It has NOTHING to do with how many decimal places are in the actual numeric value. Just where to place the decimal place when the string being read does not have one. So a value like '71' in the CSV file will be converted to the number 0.71 your printout is showing.

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

RaviSPR — Wed, 05 Jul 2023 03:25:37 GMT

Thank you for Response..

I am trying to find what are the special characters in front of those Fnds-1L & 1J.

Thank you for clarification on decimal places. I just used that informat as it is from Prod.

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

RaviSPR — Wed, 05 Jul 2023 03:33:55 GMT

Thank you for the reply.

I tried to print the Fnd values in the Log with - Put fnd= $Hex.;

I got below results.

fnd=A0314CA020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
fnd=3153202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
fnd=A0314AA0A0A0A0A0A0A0A0202020202020202020202020202020202020202020202020202020202020202020202020202020
fnd=3154202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

And so on...

Here do you mean, should I remove Leading Chars-"A0" using Compress function? But in case if fnd has Values as "A0" in the raw source file then the actual values will be deleted right if I use Compress?

Can you please suggest any solution to remove Leading Special chars?

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

Tom — Wed, 05 Jul 2023 04:50:14 GMT

Use TRANSLATE() to convert the non-breaking spaces into actual spaces.

fnd=translate(fnd,' ','A0'x);

Then use can use LEFT() to remove the leading spaces.

fnd=left(translate(fnd,' ','A0'x));

Re: Removing Leading/Trailing blanks using data Step/Proc SQL

Tom — Wed, 05 Jul 2023 05:07:16 GMT

Not sure what "from PROD" means. But unless you need to read from a source where the decimal places were deliberately removed to save one byte per value you do NOT want to include decimal places on an INFORMAT.

Since you reading a delimited file you should be reading the value in LIST mode so the width of the informat specification also does not matter. When you read in LIST mode the width of the informat is ignored and the whole next field on the line is read, whatever length it has.

The use of the COMMA informat will allow you read values that contain commas and dollar signs (also percent signs). If you want the values to PRINT in the style like 999,999,999.99 then you could attach the COMMA14.2 FORMAT to the variable with a FORMAT statement instead of an INFORMAT statement. If you could have negative values that large then COMMA15.2 might be useful. But if you need to display larger positive numbers you will need to jump to a width of 16 instead of 15 because of the extra comma needed.