proc sql union and macro

[vminc]

Hello

I have sas data sets distributed by months -example sales201401 sales 201402....

i want to retriever the data using proc sql

example

i want to the records for a particular product by month for 3 months-201401, 201402, 201403

proc sql;

select *

from sales201401

where product_id=1

union all

select *

from sales201402

where product_id=1

union all

select *

from sales201403

where product_id=1;

quit;

is there any macro technique i can use?

Thanks

vminc

[PGStats]

You could save yourself a lot of trouble with a simple datastep:

data allSales;

set sales201:;

if product_id=1;

run;

The SET statement expands dataset lists such as sales201: or sales201401-sales201408.

PG

[Reeza]

You can use macro variables to get the date intervals you need and then use a set statement in a data step.

%let start_month=201408;

%let end_month=%sysfunc(intnx(month, %sysfunc(inputn(&start_month, yymmn6.)), 3), yymmn6.);

%put &start_month;

%put &end_month;

data want;

set sales&start_month - sales&end_month;

run;

[trem]

Thanks and how to unite tables during 201412 - 201501?
In the specified example tries to unite 201413, 201414, etc.

or

%let end_month=%sysfunc(intnx(month, %sysfunc(inputn(&start_month, yymmn6.)), -24), yymmn6.);

[Howles]

Why (not) generate code with a macro?

But first, make sure the target code is right. Why the ALLs in the UNIONs?

[PGStats]

UNION ALL appends the tables without checking for duplicate records while UNION creates a set of unique records. UNION ALL is thus faster because it requires less processing. - PG

[Patrick]

If you are the one who is creating these monthly data sets then be nice to your data consumers and create views for them for the most often required windows like "current month", "current quarter", "last quarter" and so on - and update (re-create) these views whenever you create a new monthly data set. 

If you are one of the data consumers then I'd request such views as that's really not hard to code and implement.

Below some code which should give you what you have been asking for.

data sales201512 sales201601 sales201602;
  product_id=1; output;
  product_id=2; output;
run;

%macro create_view
  (
    view_name=, 
    base_name=,
    start_yyyymm=, 
    n_months=
  );

  proc sql;
    create view &view_name as
    %do i=0 %to %eval(&n_months-1);
      select *
      from &base_name%sysfunc(intnx(month, %sysfunc(inputn(&start_yyyymm, yymmn6.)), &i), yymmn6.)
      %if &i ne %eval(&n_months-1) %then
        %do;
          union all
        %end;
    %end;
    ;
  quit;

%mend;

%create_view
  (
    view_name=v_3months, 
    base_name=sales,
    start_yyyymm=201312, 
    n_months=3
  );

data test;
  set v_3months;
  where product_id=1;
run;

proc print data=v_3months;
  where product_id=2;
run;