BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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.

3 REPLIES 3
Reeza
Super User

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. 

 

 

Cynthia_sas
SAS Super FREQ

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

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

this works. Thank you

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 11086 views
  • 0 likes
  • 3 in conversation