topic Re: Caps affecting summarization of variable in SAS Programming

Caps affecting summarization of variable

G_I_Jeff — Tue, 03 Aug 2021 15:34:01 GMT

I should probably know this, but I cannot see where the difference is coming from. I run the exact same query both in all caps and lower caps. I get different results on my sum variable:

PROC SQL;                                             
 SELECT DISTINCT ZDATE FORMAT DATE9.,                 
        SUM(DDSUSIZE) AS TAPE_SUM FORMAT COMMA20.     
 FROM PDB1.NFCPLEX_TAPE                               
 WHERE DACTIVE='Y' OR ZDATE<=INTNX('DAYS',DSEXPDT,30);
QUIT;

Capitalized query produces TAPE_SUM of 406,345,495,834,624

Lower case query produces TAPE_SUM of 332,601,289,578,496

Note: This query is being run on a z/OS mainframe platform. I thought that all variable values were stored as capital EBCDIC characters. If I change the 'y' to uppercase when running query in all lowercase I get the 406,345,495,834,624 summed value. If I run the following:

proc sql;                 
 select distinct dactive  
   from pdb1.nfcplex_tape;

I get either blank or Y values returned.

Re: Caps affecting summarization of variable

jimbarbour — Tue, 03 Aug 2021 16:03:15 GMT

Have you tried adding the UPCASE function?

WHERE UPCASE(DACTIVE)='Y'

Jim

Re: Caps affecting summarization of variable

G_I_Jeff — Tue, 03 Aug 2021 16:47:34 GMT

Hi Jim,

I've changed the value of 'y' to 'Y' and get the desired/correct results. My questions lies with why am I getting different values with the capitalization of the queried value?

Re: Caps affecting summarization of variable

PaigeMiller — Tue, 03 Aug 2021 16:49:28 GMT

Capital Y is considered different than lowercase y

Re: Caps affecting summarization of variable

G_I_Jeff — Tue, 03 Aug 2021 16:56:55 GMT

Paige Miller, I agree 100%. But why am I getting a summarization value when I run the query in lower caps if all the values of DACTIVE are capital Y?

Re: Caps affecting summarization of variable

jimbarbour — Tue, 03 Aug 2021 17:07:14 GMT

@G_I_Jeff wrote:
Paige Miller, I agree 100%. But why am I getting a summarization value when I run the query in lower caps if all the values of DACTIVE are capital Y?

Well, maybe they aren't. Remember that the display settings on a 3270 terminal emulator can be set such that 'y' will display as 'Y'. Not sure if that's the case here, but it could be.

Have you tried running a Proc Freq on DACTIVE?

Jim

Re: Caps affecting summarization of variable

PaigeMiller — Tue, 03 Aug 2021 17:27:50 GMT

Is there a format applied to the variable, according to PROC CONTENTS?