BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chanduk
Obsidian | Level 7

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;

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
chanduk
Obsidian | Level 7

Hi ,

 

Please see attached screen shot file for proc contents.

 

Regards,

chandu

View solution in original post

12 REPLIES 12
Cynthia_sas
SAS Super FREQ
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
SAS Super FREQ

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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
SAS Super FREQ

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

Hi ,

 

Please see attached screen shot file for proc contents.

 

Regards,

chandu

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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