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.
Capital Y is considered different than lowercase y
@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
Is there a format applied to the variable, according to PROC CONTENTS?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.