topic PROC REPORT WITH CONDITION in ODS and Base Reporting

PROC REPORT WITH CONDITION

JHE — Thu, 26 Oct 2017 00:06:51 GMT

I need to have a report, only need past 12 month data, is there a way from PROC Report to achieve it.

My Code:

data _null_;
    dMEnd=intnx('month', today(), -12, 'e');
    Call symput('dtMEnd', PUT(dMEnd, YYMMD.));
run;

%PUT &dtMEnd;

PROC REPORT DATA=TPLMEM.TPL_SUMMARY_0100 (MONTH >="&dtMEnd");
    COLUMN MONTH Category OVERALL_MEMBERSHIP TPL MEDICARE MEDICARE_SUPPLEMENTAL 
        MEDICARE_A MEDICARE_B MEDICARE_AB MEDICARE_ADVANTAGE COMMERCIAL VISION DENTAL 
        LIMITED_BENEFITS Casualty PHARMACY;
    DEFINE MONTH/GROUP;
    DEFINE MONTH/ORDER descending;
    title " LOB 0100 TPL Membership";
    footnote" Run at &sDay &stime";
RUN;

The code would not working... how to ? need advise.

Re: PROC REPROT WITH CONDITION

Reeza — Wed, 25 Oct 2017 23:57:51 GMT

You have a couple of issues, at first glance.

1. You don't have the keyword WHERE to indicate you're trying to filter the data set.

2. Your date isn't formatted correctly, recall how SAS needs dates specified, in a DATE9 format.

Re: PROC REPORT WITH CONDITION

Cynthia_sas — Thu, 26 Oct 2017 00:16:59 GMT

Hi:

You may have specified your condition incorrectly. Since you did not post data, I made a few examples using SASHELP.CLASS. Use either a WHERE Statement or a WHERE dataset option.

%let wantage = 15;

proc report data=sashelp.class;
  title 'Where Statement';
  where age ge &wantage;
run;

proc report data=sashelp.class(where=(age ge &wantage));
  title 'Where DATASET Option';
run;

You are using a DATA step before your PROC REPORT to generate a character string that looks like this:

So how your condition will work depends on the format of the variable MONTH in your data. Right now, you are treating the macro variable in the comparison as a text string:

where month ge = "2016-10";

if your MONTH variable is a character string in the form YYYY-MM, then once you get the WHERE specified correctly, you should get hits, if the data has observations with that value. However, if MONTH is a SAS date value, then you are specifying the WHERE condition incorrectly.

When you do a PROC CONTENTS on the data TPLMEM.TPL_SUMMARY_0100 what is the type for MONTH -- character or numeric. If numeric, is there a SAS date format associated with the variable?

If MONTH is a standard date variable, containing a number that represents the number of days since Jan 1, 1960, then a better way to write the WHERE is to use the date constant representation of the date, as shown in the code below:


data testdata;
  infile datalines;
  input month : mmddyy10. name $ amount;
return;
datalines;
04/15/2016 alan 100
08/17/2016 barb 200
10/02/2017 carl 300
11/15/2017 dana 400
04/12/2017 arla 100
08/15/2017 bill 200
10/05/2017 cathy 300
11/29/2017 dave 400
;
run;

proc report data=testdata;
  where month ge "01Oct2016"d;
  define month / group f=yymmd8.;
run;

Hope this helps,

Cynthia

Re: PROC REPORT WITH CONDITION

JHE — Mon, 30 Oct 2017 15:29:03 GMT

this works. Thank you