Hi ,
Below is the data set UMappeallog .I would like to print the report using proc report but I got the 0 records .Is my code representation is ok in where clause in proc report? If not how I can write the code for getting 3 records in the report.
dataset:UMappeallog
==========
provider_id Case_id valid_from_date
p001 Coo1 03Apr2017
p002 Coo2 03Apr2017
p003 Coo3 03Apr2017
Code:
/* below macro variables OPEN_DATE_MIN,OPEN_DATE_MAX coming from date selection in prompts */
%let OPEN_DATE_MIN=19apr2016;
%let OPEN_DATE_MAX=19apr2017;
proc report data=UMAppealLog1 nowd split='*';
where valid_from_date between "&open_date_min" and "&open_date_max";
column provider_id Case_id ;
define provider_id/"provider id";
define Case_id/"Case id";
run;
Hi ,
Please see attached screen shot file for proc contents.
Regards,
chandu
Hi
A few things to consider:
Sending the log will help in figuring out what is wrong.
Bruno
Hi Bruno,
Below is the code for converting numeric date value into character value .Valid_from_dt is the sas date value .Without converting character value , how to check the Valid_from_date between "&open_date_min" and "&open_date_max". I mentioned below sample log.
data UMAppealLog1;
set UMAppealLog;
Valid_from_date=strip(put(Valid_from_dt,date9.));
run;
log:
===
NOTE: No observations were selected from data set WORK.UMAppealLog1.
NOTE: No observations in input data set.
NOTE: There were 0 observations read from the data set WORK.UMAPPEALLOG1.
WHERE (valid_from_date>='19apr2016' and valid_from_date<='19apr2017');
Hi Bruno,
Below is the code for converting numeric date value into character value .Valid_from_dt is the sas date value .Without converting character value , how to check the Valid_from_date between "&open_date_min" and "&open_date_max". I mentioned below sample log.
data UMAppealLog1;
set UMAppealLog;
Valid_from_date=strip(put(Valid_from_dt,date9.));
run;
log:
===
NOTE: No observations were selected from data set WORK.UMAppealLog1.
NOTE: No observations in input data set.
NOTE: There were 0 observations read from the data set WORK.UMAPPEALLOG1.
WHERE (valid_from_date>='19apr2016' and valid_from_date<='19apr2017');
Regards,
chandu
Hi,
Below is the log after changing little bit on proc report.Please see the error.
28
29 %let OPEN_DATE_MIN=19apr2016;
30 %let OPEN_DATE_MAX=19apr2017;
31 TITLE1 "UM Appeal Log";
32 TITLE2 "From &OPEN_DATE_MIN. To &OPEN_DATE_MAX.)";
33
34 proc report data=UMAppealLog1 nowd split='*';
35 where valid_from_date between "&open_date_min"d and "&open_date_max"d;
2 The SAS System 00:02 Wednesday, April 19, 2017
ERROR: WHERE clause operator requires compatible variables.
36 column x_f_b_prov_id x_f_b_prov_nm case_id x_p_recoup_amt x_b_no_analyst_nm X_P_AFTR_REBUTTAL_AMT x_b_admin_ar
37 X_B_ADMIN_RV_INFO X_B_FAIR_DT X_B_FAIR_COMM X_B_DIS_DT_DECISION X_B_DIS_COURT_COMM X_B_APPEAL_LOG;
38 define x_f_b_prov_id/"Provider ID";
39 define x_f_b_prov_nm/"Provider Name"
Regards,
chandu
Use proc contents to determine the attributes of variable valid_from_date.
And post some examples for the values contained in valid_from_date. As it looks, you have your dates stored as character, which is A BAD IDEA.
@chanduk wrote:
Hi,
Below is the log after changing little bit on proc report.Please see the error.
28
29 %let OPEN_DATE_MIN=19apr2016;
30 %let OPEN_DATE_MAX=19apr2017;
31 TITLE1 "UM Appeal Log";
32 TITLE2 "From &OPEN_DATE_MIN. To &OPEN_DATE_MAX.)";
33
34 proc report data=UMAppealLog1 nowd split='*';
35 where valid_from_date between "&open_date_min"d and "&open_date_max"d;
2 The SAS System 00:02 Wednesday, April 19, 2017ERROR: WHERE clause operator requires compatible variables.
36 column x_f_b_prov_id x_f_b_prov_nm case_id x_p_recoup_amt x_b_no_analyst_nm X_P_AFTR_REBUTTAL_AMT x_b_admin_ar
37 X_B_ADMIN_RV_INFO X_B_FAIR_DT X_B_FAIR_COMM X_B_DIS_DT_DECISION X_B_DIS_COURT_COMM X_B_APPEAL_LOG;
38 define x_f_b_prov_id/"Provider ID";
39 define x_f_b_prov_nm/"Provider Name"
Regards,
chandu
Hi
The variable name Valid_from_dt seems to indicate, that the "valid from" variable is a datetime value.
As @Kurt_Bremser mentioned please post the output of the Proc Contents data=UMAppealLog; as well as some values.
If valid_from_dt is actually a datetime value, you can use the DATEPART() function to extract the date and use a date constant like "19apr2017"d in your where statement.
Bruno
Hi ,
Please see below complete code and also see the log . Still I am getting error.
proc sql;
create table UMAppealLog as
select a.x_f_b_prov_id,
a.x_f_b_prov_nm,
b.case_id,
b.CASE_TYPE_CD,
datepart(b.valid_from_dttm) as Valid_from_dt,
c.x_p_recoup_amt,
c.X_P_AFTR_REBUTTAL_AMT,
d.x_b_no_analyst_nm,
e.x_b_admin_ar,
e.X_B_ADMIN_RV_INFO,
e.X_B_FAIR_DT,
e.X_B_FAIR_COMM,
e.X_B_DIS_DT_DECISION,
e.X_B_DIS_COURT_COMM,
e.X_B_APPEAL_LOG,
f.x_b_case_status
from ecm_rpt.c_findings a,
ecm_rpt.case_pivot b,
ecm_rpt.c_recoupment c,
ecm_rpt.c_peer_review d,
ecm_rpt.c_x_appeal e,
ecm_rpt.c_referred_from f
where a.case_rk=b.case_rk
and b.case_rk=c.case_rk
and c.case_rk=d.case_rk
and d.case_rk=e.case_rk
and e.case_rk=f.case_rk
and b.CASE_TYPE_CD eq 'PROV'
and e.X_B_APPEAL_INFO='1'
and f.x_b_case_status ne 'Closed'
;
quit;
/*data UMAppealLog1;*/
/*set UMAppealLog;*/
/*Valid_from_date=strip(put(Valid_from_dt,date9.));*/
/*run;*/
%let OPEN_DATE_MIN=19apr2016;
%let OPEN_DATE_MAX=19apr2017;
TITLE1 "UM Appeal Log";
TITLE2 "From &OPEN_DATE_MIN. To &OPEN_DATE_MAX.)";
proc report data=UMAppealLog nowd split='*';
where "valid_from_dt"d between "&open_date_min" and "&open_date_max";
column x_f_b_prov_id x_f_b_prov_nm case_id x_p_recoup_amt x_b_no_analyst_nm X_P_AFTR_REBUTTAL_AMT x_b_admin_ar
X_B_ADMIN_RV_INFO X_B_FAIR_DT X_B_FAIR_COMM X_B_DIS_DT_DECISION X_B_DIS_COURT_COMM X_B_APPEAL_LOG;
define x_f_b_prov_id/"Provider ID";
define x_f_b_prov_nm/"Provider Name";
define case_id/"Case ID";
define x_p_recoup_amt/"Issue Account * Receivable#";
define x_b_no_analyst_nm/"UM Analyst";
define X_P_AFTR_REBUTTAL_AMT/"Administration*Reconsideration*Amt";
define x_b_admin_ar/"AR LETTER DATE";
define X_B_ADMIN_RV_INFO/"AR DECISIONS";
define X_B_FAIR_DT/"Fair Hearing*Decision Letter Date";
define X_B_FAIR_COMM/"Fair Hearing*Decision Comments";
define X_B_DIS_DT_DECISION/"District Court * Decision Date";
define X_B_DIS_COURT_COMM/"District Court*Comments";
define X_B_APPEAL_LOG/"Appeal Comments ";
run;
===========
LOG
=========
69 proc report data=UMAppealLog nowd split='*';
70 where "valid_from_dt"d between "&open_date_min" and "&open_date_max";
ERROR: Invalid date/time/datetime constant "valid_from_dt"d.
ERROR: Syntax error while parsing WHERE clause.
Completely explained by the error message:
ERROR: Invalid date/time/datetime constant "valid_from_dt"d.
"valid_from_dt"d is in no way a date value. A SAS date litteral, indicated by use of "somethng"d must be in either DATE7 or Date9 appearance.
"01JAN2017"d or "01JAN17"d are valid types
furthermore
between "&open_date_min" and "&open_date_max";
would fail because these are missing the d
between "&open_date_min"d and "&open_date_max"d;
As mentioned multiple times provide proc contents results for your variable Valid_from_dt. The contents of that variable will be needed to create a valid Where clause.
Hi ,
Please see attached screen shot file for proc contents.
Regards,
chandu
Please show some of the values of valid_from_date, so we can see how the presumed date values are stored.
Valid_from_dt in the proc contents is shown as type NUM and no date informat or format. So it is very likely not an actual date value but a numeric like 10152014 immitating a date. So we need to see exact values to get an idea of how to make it an actual date.
Likely will be something like one of these
datevalue = input (put(valid_from_dt, z8.),mmddyy8.)
datevalue = input (put(valid_from_dt, z8.),ddmmyy8.)
datevalue = input (put(valid_from_dt, z8.),yymmdd8.)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.