10-25-2017 07:06 PM - last edited on 10-25-2017 08:06 PM by Reeza
I need to have a report, only need past 12 month data, is there a way from PROC Report to achieve it.
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.
10-25-2017 07:57 PM
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.
10-25-2017 08:16 PM - edited 10-25-2017 08:16 PM
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,