Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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

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;

 

 

 

 

 


Accepted Solutions
Solution
‎04-19-2017 02:09 PM
Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Hi ,

 

Please see attached screen shot file for proc contents.

 

Regards,

chandu

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,862

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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
SAS Super FREQ
Posts: 708

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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

Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Posted in reply to Bruno_SAS

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

Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Posted in reply to Bruno_SAS

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

Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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

Super User
Posts: 7,758

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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


 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 708

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Hi

 

The variable name Valid_from_dt seems to indicate, that the "valid from" variable is a datetime value.

 

As @KurtBremser 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

Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Posted in reply to Bruno_SAS

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.

Super User
Posts: 11,343

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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.

Solution
‎04-19-2017 02:09 PM
Contributor
Posts: 25

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

Hi ,

 

Please see attached screen shot file for proc contents.

 

Regards,

chandu

Super User
Posts: 7,758

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Zero records displaying while running proc report while using prompt values in SAS Stored proces

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 187 views
  • 2 likes
  • 5 in conversation