Error: Variable not Found

[Cykelops]

Hi,

Restarting my topic here.  I have an issue where I have encountered an error. I'm not familiar with SAS but I would like to have it fix.

Below is the error's snapshot and code.

 

PROC IMPORT OUT= PledgeDataLoanLevel8
              DATAFILE= "&importfolder./CTL_2_CD 300 - &hereportdate. FRBNY Waterfall.xlsx"
            DBMS=XLSX 
                                                REPLACE;
     GETNAMES=YES;   SHEET = 'Breakout'; 
RUN;

data  PledgeDataLoanLevel8;
set  PledgeDataLoanLevel8;
if src_sys_tx in ('') then delete;
if src_sys_tx in ('Total', 'Prior Month') then delete;
if loan_nb = . then delete; 
run;

proc freq data = PledgeDataLoanLevel4 ; tables final_waterfall   ;run;
proc freq data = PledgeDataLoanLevel5 ; tables waterfall_frb_ny   ;run;
proc freq data = PledgeDataLoanLevel6 ; tables final_waterfall   ;run;
proc freq data = PledgeDataLoanLevel7 ; tables waterfall   ;run;
proc freq data = PledgeDataLoanLevel8 ; tables waterfall_frb_ny   ;run;

proc sql;
select
final_waterfall ,
sum  (upb) as upb format = comma20.2       /**  FHLB SF WAMU HE ***/  
from PledgeDataLoanLevel4
group by   final_waterfall 
order by   final_waterfall 
;quit; 

Hope to hear from experts of the possible solutions.

Cykelops

[ballardw]

If the variable isn't there what do you expect us to do???

Show us the results of Proc Contents for that data set. Maybe the name is misspelled.

Note: Proc IMPORT is a guessing procedure. The results for things like variables names can be problematic. If the previous data set you read with Proc Import had one spelling for the column that created the variable that may not be the case for the latest. Or someone may have changed the contents a LOT and you won't know it until you examine the actual data set generated.

[Cykelops]

HI @ballardw 

see proc contents below. Not sure how to look at it and how we can determine the correct spelling.

 

Please advise. Hope this helps.

[antonbcristina]

The PROC CONTENTS report contains at the bottom a table titled Alphabetical List of Variables and Attributes. From the screenshot, it doesn't look like waterfall_frb_ny is a variable in the PledgeDataLoanLevel5 dataset hence the error. 

[Cykelops]

Hi @antonbcristina @ballardw 

Since this "waterfall_frb_ny" does not really exist. I would like to know if this is something that is being created or do I need to look for an excel or database (.dat) to verify other tables like Final Waterfall. I like to know why it is not existing. I run this report last month and I don't have any issue. While I really don't know how to read SAS code, maybe you could guide me in investigating it.

Hoping for your reply..

Cykelops

[Tom]

If you ran the same code then the inputs have changed.

Your code seems to be reading in data from EXCEL files.  So the EXCEL files are different.

[antonbcristina · Accepted solution]

In the first screenshot you provided, it seems like each PledgeDataLoanLevelx dataset is being created from a PROC IMPORT step above. Look for the PROC IMPORT step that created PledgeDataLoadLevel5 and locate the source file being imported (they seem to be Excel spreadsheets). You'll want to open this source file and look for the variable names. Chances are this source file has been updated and the variable names no longer match. 

From the PROC FREQ steps you shared, it seems that some of these source files have a final_waterfall or waterfall_frb_ny variable. Change the PROC FREQ step to use the appropriate variable.  

[Cykelops]

HI @antonbcristina @Tom 

Thank you both of your explanation. I was able to check the excel file uploaded in SAS Viya and there is a difference compared to last month. Under the tab FRBNY HE at column D, there is a header name "Final_Waterfall" while last month the header name is "WATERFALL_FRB_NY". I think this the reason why "Waterfall_FRB_NY" variable is missing.

I have learned a lot today especially the use of PROC Contents. Thanks to both of you.

Cykelops

[ballardw]

---

@Cykelops wrote:

Hi @antonbcristina @ballardw 

Since this "waterfall_frb_ny" does not really exist. I would like to know if this is something that is being created or do I need to look for an excel or database (.dat) to verify other tables like Final Waterfall. I like to know why it is not existing. I run this report last month and I don't have any issue. While I really don't know how to read SAS code, maybe you could guide me in investigating it.

Hoping for your reply..

Cykelops

---

Perhaps the real question here is why do you think there should be a variable "waterfall_frb_ny" at all.

I am going to guess that you were given this code by someone else. Which means that you have very likely fallen to one of the issues with spreadsheets: people change them. It might be related to a similar project and may even have the same file names but what is in the file has changed. If it isn't there you can't use it.

If the only place that this variable is mentioned is in that one piece of code then just don't run it. If it is used anywhere else need to go to whoever assigned this activity and tell them pieces are missing. Take the Proc Contents results when you do. You may need to run Proc Contents on all of those data sets to make sure they contain the variables expected of them.

If you want us to create a waterfall_frb_ny you would have to provide the rules and the base data, or at least examples there of, on how it is to be created.

I have one project I have been working with for going on 12 years. On average one or more of the spreadsheets the data source extracts data to changes every three months. And these are generated by someone's programs. If people are manually entering data the problems with things like changed column headers (i.e. variable names), missing data, improperly entered data get much worse.

Even when the variable contents don't change they have changed the order of columns in the data, which is a headache as I use data steps to read them because it is the only way to make sure things like variable types and lengths don't change from file to file.

They also change the number of header rows periodically.

I have even received files that part  way through the file a column that is supposed to be a date of enrollment field starts appearing as currency, or currency that becomes dates. Consider, what is 2023-03-15 supposed to mean in terms of $? ($45,000 actually)