Hi:
SAS variable names are NOT case sensitive. For example, you could refer to your variable as var14, VaR14, VAR14, vAR14, or any combination thereof and the variable reference would be recognized.
However, for comparison purposes, such as IF and WHERE statements, case is an issue. There are however, programming techniques you can use to make case irrelevant. So, for example, this:
WHERE upcase(var14) = 'AUTOPAY TRANSACTIONS';
would cause the UPCASE function to be used for the purpose of the comparison, so that no matter what case was -inside- the variable value, your comparion would be performed on an uppercase value. Also, what if for the first 100 rows or so in your worksheet, folks were really good and typed in "Autopay Transactions" but then for about another 500 rows, they typed only "Autopay Transaction" (without the S) and then for another 1000 rows, they typed just "Autopay" and then for the rest of the rows, they entered "AT"??? In order to select all of these possible values, you will have to code the WHERE or IF statement accordingly. PROC FREQ will show you exactly what is in the VAR14 variable.
Do you know, unequivocally, that "Autopay Transactions" occurs -exactly- like that on every row that you want to select??? Proc Freq will show you -exactly- what is inside VAR14. What if you miscounted the columns and VAR14 does not contain 'Autopay Transactions' -- maybe it's VAR13 or VAR15. It is possible that the columns in Excel did not get read in as you expected or that the variable numbers are not as you expect.
What if you had this in the Excel sheet:
[pre]
A B C D E<--Excel col nums
1 VAR1 VAR2 VAR3 VAR4 VAR5
2 Regular Transactions
3 alan 111 111 111
4 bob 222 222 222
5 carl 333 333 333
6 Autopay Transactions
7 dave 444 444 444
8 edwin 555 555 555
^
|Excel row nums
[/pre]
In the above "sheet simulation" VAR1 shows a category of either Regular Transactions or Autopay Transactions. Rows 3, 4 and 5 are the Regular Transactions and rows 7 and 8 are the Autopay Transactions. However, when SAS reads in data that looks like this, SAS thinks that VAR1 is missing or blank for rows 3, 4, 5, 7 and 8.
Without knowing what was originally in your worksheet, it is impossible to tell why your WHERE statement is NOT working. However, using PROC FREQ will allow you to explore the imported data to make sure that the data got imported into the variables correctly and WHAT is in each field.
Consider the following PROC PRINT:
[pre]
proc print data=sashelp.class;
title 'Eighteen year old students';
where age = 18;
run;
[/pre]
When I run that PROC PRINT, the WHERE statement will result in this message in the log:
[pre]
207 proc print data=sashelp.class;
208 title 'Eighteen year old students';
209 where age = 18;
210 run;
NOTE: No observations were selected from data set SASHELP.CLASS.
NOTE: There were 0 observations read from the data set SASHELP.CLASS.
WHERE age=18;
[/pre]
But, if I had run a PROC FREQ before hand, I would have already known that nobody was age 18 in the data:
[pre]
LOG:
212 proc freq data=sashelp.class;
213 tables age;
214 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.59 seconds
cpu time 0.00 seconds
OUTPUT:
Cumulative Cumulative
Age Frequency Percent Frequency Percent
--------------------------------------------------------
11 2 10.53 2 10.53
12 5 26.32 7 36.84
13 3 15.79 10 52.63
14 4 21.05 14 73.68
15 4 21.05 18 94.74
16 1 5.26 19 100.00
[/pre]
cynthia
Click image to register for webinar
