topic proc sql union and macro in SAS Programming

proc sql union and macro

vminc — Fri, 26 Dec 2014 18:49:47 GMT

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

Re: proc sql union and macro

PGStats — Fri, 26 Dec 2014 19:16:13 GMT

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.

Re: proc sql union and macro

Reeza — Fri, 26 Dec 2014 22:43:03 GMT

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;

Re: proc sql union and macro

Howles — Sat, 27 Dec 2014 02:55:29 GMT

Why (not) generate code with a macro?

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

Re: proc sql union and macro

PGStats — Sat, 27 Dec 2014 03:43:24 GMT

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

Re: proc sql union and macro

trem — Wed, 05 Oct 2016 07:23:07 GMT

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

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

Re: proc sql union and macro

Patrick — Wed, 05 Oct 2016 08:29:36 GMT

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;