Zero records displaying while running proc report while using prompt values in SAS Stored process

[chanduk]

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;

[Cynthia_sas]

Hi, offhand, my guess would be that your WHERE statement is incorrect. if you remove the WHERE statement, you can test whether the PROC REPORT is working. Then you can fix the WHERE. My guess is that without the WHERE you will see all the observations in the data.

If your variable VALID_FROM_DATE is a SAS numeric variable that is formatted as a date, then you need to use the date constant syntax for specifying a date:
where myvar = "&wantdate"d; or
where myvar between "01jan2017"d and "05jan22017"d;

cynthia

[BrunoMueller]

Hi

A few things to consider:

• what is the type for variable valid_from_date, char or numeric (SAS date)
• having dates represented as char value is not recommend
• character comparison are always case sensitive, maybe that is why no rows are returned

Sending the log will help in figuring out what is wrong.

Bruno

[chanduk]

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');

[chanduk]

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

[chanduk]

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

[Kurt_Bremser]

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, 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

---

[BrunoMueller]

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

[chanduk]

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.

[ballardw]

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.

[chanduk · Accepted solution]

Hi ,

Please see attached screen shot file for proc contents.

Regards,

chandu

[Kurt_Bremser]

Please show some of the values of valid_from_date, so we can see how the presumed date values are stored.

[ballardw]

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.)