The SAS Output Delivery System and reporting techniques

PROC REPORT WITH CONDITION

Reply
Frequent Contributor
Frequent Contributor
Posts: 118

PROC REPORT WITH CONDITION

[ Edited ]

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.

Super User
Posts: 22,820

Re: PROC REPROT WITH CONDITION

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. 

 

 

SAS Super FREQ
Posts: 9,253

Re: PROC REPORT WITH CONDITION

[ Edited ]

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:

macro_var_value.png

 

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

 

Frequent Contributor
Frequent Contributor
Posts: 118

Re: PROC REPORT WITH CONDITION

Posted in reply to Cynthia_sas

this works. Thank you

Ask a Question
Discussion stats
  • 3 replies
  • 321 views
  • 0 likes
  • 3 in conversation