Hi,
I am totally new to SAS and I wonder if someone could help me fix the error I encountered. Kindly see error below.
Thanks,
Cykelops
In the PROC FREQ code the variable you listed in the TABLES statement does not exist in the dataset you told it to use with the DATA= option on the PROC statement.
You will have to look at your datasets to decide whether you have the dataset name wrong or the variable name wrong.
Note: Next time share the text from of your code and your LOG rather than posting a photograph of the text. You can use the INSERT CODE button on the forum editor to let you paste in the text into a pop-up box so that the forum preserves the formatting and does not try to reflow the text into paragraphs.
Hi @Tom
Thanks for the feedback. Sorry for that for I am not quite familiar on how to use this one. Anyway, here is the code. I hope this is enough to what you are looking for.
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;
Looking forward for your response.
Cykelops
So you used PROC IMPORT to convert a sheet in an XLSX workbook into the SAS dataset named PledgeDataLoanLevel8.
But then later on you are trying to use other datasets with different names. For example
proc freq data = PledgeDataLoanLevel5 ; tables waterfall_frb_ny ;run;
If you want to see what variables are in that dataset use PROC CONTENTS.
proc contents data = PledgeDataLoanLevel5 ;
run;
Hi @Tom
Actually I have that "PledgeDataLoanLevel15" on the upper part of the code. Is this what you are looking for?
PROC IMPORT OUT= PledgeDataLoanLevel4 DATAFILE= "&importfolder./&reportMonth. MBandAMCIO Waterfalls.xlsx" DBMS=XLSX REPLACE; GETNAMES=YES; SHEET = 'FHLBSF WaMu HE'; RUN; /*** FRB NY HE ***/ PROC IMPORT OUT= PledgeDataLoanLevel5 DATAFILE= "&importfolder./&reportMonth. MBandAMCIO Waterfalls.xlsx" DBMS=XLSX REPLACE; GETNAMES=YES; SHEET = 'FRBNY HE'; RUN; /*** FHLB WAMU Mortgages ***/
Please advise
You would have gotten a different message if the dataset did not exist.
Examples:
1 proc freq data=sashelp.class; tables sex; run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: PROCEDURE FREQ used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 2 proc freq data=sashelp.class; tables no_such_variable ; run; ERROR: Variable NO_SUCH_VARIABLE not found. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE FREQ used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 3 proc freq data=no_such_dataset; tables sex; run; ERROR: File WORK.NO_SUCH_DATASET.DATA does not exist. ERROR: No data set open to look up variables. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE FREQ used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
You need to LOOK at the dataset to see what names PROC IMPORT invented to hold the vlaues of the columns in Excel worksheet.
Note that if you have accidentally set the VALIDVARNAME option to ANY instead of V7 then PROC IMPORT might have made the name of the variable using spaces or other invalid characters. In that case you will need to use a name literal in your code for any variable whose name does not follow the normal naming rules (letter, digits and underscores only. Does not start with a digit.).
proc freq data = PledgeDataLoanLevel5 ; tables 'waterfall frb ny'n ;run;
Hi @Tom
Tried using below and it still gave me an error.
proc freq data = PledgeDataLoanLevel5 ; tables 'waterfall frb ny'n ;run;
proc freq data = PledgeDataLoanLevel4 ; tables final_waterfall ;run; proc freq data = PledgeDataLoanLevel5 ; tables 'waterfall frb ny'n ;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;
Please advise.
@Cykelops wrote:
Hi @Tom
Tried using below and it still gave me an error.
proc freq data = PledgeDataLoanLevel5 ; tables 'waterfall frb ny'n ;run;
proc freq data = PledgeDataLoanLevel4 ; tables final_waterfall ;run; proc freq data = PledgeDataLoanLevel5 ; tables 'waterfall frb ny'n ;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;Please advise.
Your data set PledgeDataLoanLevel5 only has these variables unless your proc contents was run on a different data set:
So the ONLY variables you can use in this data set are named Balance, Final_waterfall, loan_number and Source. Period. Those are the only names in the set. There is NOTHING resembling waterfall_frb_ny at all.
If there is supposed to be one in that data set the go talk to who provided the data sources as it is not there and cannot be used.
Using the proc contents data = PledgeDataLOanLevel5; run;, variables are below
Please advise.
@Cykelops wrote:
Using the proc contents data = PledgeDataLOanLevel5; run;, variables are below
Please advise.
So which of those variables to you want to get the frequencies for? Of the four to me ones that seem most likely to have values where it makes sense to use PROC FREQ on are FINAL_WATERFALL and SOURCE.
From now on @Cykelops you want to post in some other location within communities.sas.com, such as "New SAS Users". You posted in "All Things Community" which is about using the forum and not about SAS coding.
HI @PaigeMiller
Apology, as beginner of this site, I was not able to locate the start topic. I just found it in this category. Next time to start with the New Users.
Thanks.
Cykelops
About 80 percent of the time when I get a "variable not found" or "data set doesn't exist" message it is a spelling or memory problem. Maybe one of the _ characters isn't actually in the name, or there are two.
You can get output in your results window by running:
Proc contents data=<dataset name goes here>; run;
to describe your data set. Example you can run as you should have the SAS supplied training data set SASHELP.CLASS
Proc contents data=sashelp.class; run;
Where you are using multiple data sets with similar names you might be forgetting which data set has which variables depending on why you have the similar names.
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!