DATA Step, Macro, Functions and more

proc sql union and macro

Reply
Occasional Contributor
Posts: 5

proc sql union and macro

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

Respected Advisor
Posts: 4,644

Re: proc sql union and macro

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

PG
Super User
Posts: 17,801

Re: proc sql union and macro

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;

Regular Learner
Posts: 1

Re: proc sql union and macro

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.);
Regular Contributor
Posts: 184

Re: proc sql union and macro

Why (not) generate code with a macro?

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

Respected Advisor
Posts: 4,644

Re: proc sql union and macro

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

PG
Respected Advisor
Posts: 3,887

Re: proc sql union and macro

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;
Ask a Question
Discussion stats
  • 6 replies
  • 1015 views
  • 0 likes
  • 6 in conversation