Caps affecting summarization of variable

[G_I_Jeff]

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.

[jimbarbour]

Have you tried adding the UPCASE function?

WHERE UPCASE(DACTIVE)='Y'

Jim

[G_I_Jeff]

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?

[PaigeMiller]

Capital Y is considered different than lowercase y

[G_I_Jeff]

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?

[jimbarbour]

---

@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

[PaigeMiller]

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