combining multiple tables(qty=various) in one dataset/table by using macro.

[Ivan555]

Hello!

I have a goal to craft one table as an output of a several ones. The quantity of tables being combined may differ.

In accordance with the code, I would like to make a data "want" , which will include in itself all information of the tables "A11", being formed during the process. The number of tables in not being fixed(in example qty=2) and may vary, so as a soluting i see a macro with do function.

But.. I am thinking already rather much time, but i still cant catch an idea how could I to match all tables into one...

If anyone can help, i'll be very grateful.

(version SAS 9.4)

data input_dates;
length dt $9;
input dt;
dtnum +1;
datalines;
01JAN1950
01JUL1950
;
run;

proc sql noprint; select count(*) into: dtcnt from input_dates; quit;

%macro aaa;
%do i=1 %to &dtcnt;
proc sql noprint; select dt into: rdate from input_dates where dtnum = &dtcnt; quit;

proc sql;
    create table a1 as
    select date, air
    from sashelp.airline
    where date < "&rdate."d
    order by date desc;

    create table a11 as
    select date, air
    from a1(firstobs=1 obs=1);
quit;

data want;
merge a11;  /*i want here(or mabe in another place if need) to merge somehow all the tables with names a11*/
run;

%end
%mend aaa

%aaa

TY!

[Patrick]

@Ivan555 

I don't really understand what you are trying to achieve with your code. But you can use the colon modifier as a wildcard to just address all tables that start with A11.

data a11something;
  a=1;output;stop;
run;
data a11somethingelse;
  a=2;output;stop;
run;

data want;
  set a11:;
run;

[Ivan555]

understood, thanks, i'll try to explain my idea more carefully.

at start i do have
> a number of dates (input_dates), in example their quantity is 2, but in life it can vary.
> a table (sashelp.airline)
> and a condition on the table: to take most actual date < each input_date  according with the table - this is a result of A11.

the goal - is to save somewhere all the data from A11 tables(which are being rewrighting)

[Patrick]

@Ivan555 

"and a condition on the table: to take most actual date < each input_date"

You have two input dates. What should happen if for a specific row in sashelp.airline the "actual date" is smaller than both input dates. Would you want to select the row once or twice? Would you want one or two rows in A11?

On a side note: SAS Macro language is certainly not the right thing for your problem. This is about looking up values in a table for row selection in another table - something to be done via SAS data step or SQL.

[Ivan555]

@Patrick 

According with example I do have

1. date 01DEC1949 (received as the result of select on 01JAN1950)
2. date 01JUN1950 (received as the result of select on 01JUL1950)

if 01DEC1949 < 01JUL1950(or 01JUN1950) - I am fine, this matters nothing for me
if we will take another example like dates (01JAN1950, 02JAN1950) and as the result of select we will get the same row (01DEC1949) for each of them, I am still fine with getting this row twice(plus this will happen seldom, the analog of "sashelp.airline" from my internal library is very dense)

I am pretty new both in SAS and in SQL(working with them like for a month or two), so perhaps you are right.. I just thought macro is prefered in the case that the number of input dates may differ.. In life there can be 2 input dates, can be 10, can be 50..

[Ivan555 · Accepted solution]

seems i've done what i want.. 

don't know, mabe my code is not effective.. but seems it works.. solvation seems was in "proc append".

(data want = data output_dates in my code)

data input_dates;
length dt $9;
input dt;
dtnum +1;
datalines;
01JAN1950
01JUL1950
;
run;

data output_dates;
input DATE;
format DATE date9.;
input AIR;
format AIR int;
datalines;
;

proc sql noprint; select count(*) into: dtcnt from input_dates; quit;

%macro aaa;
%do i=1 %to &dtcnt;
proc sql noprint; select dt into: rdate from input_dates where dtnum = &i; quit;

proc sql;
    create table a1 as
    select date, air
    from sashelp.airline
    where date < "&rdate."d
    order by date desc;

    create table a11 as
    select date, air
    from a1(firstobs=1 obs=1);
quit;

proc append base = output_dates data = a11;
run;

%end
%mend aaa

%aaa

[Patrick]

@Ivan555 

The golden rule is: Only use SAS Macro code if you can't do it with regular SAS code.

For your problem wouldn't below do it as well?

data input_dates;
  attrib dt length=8 format=date9.;
  input dt : date9.;
  datalines;
01JAN1950
01JUL1950
;
run;

proc sql;
  create table a11 as
  select date, air
  from sashelp.air o
  where exists
    (select * from input_dates i where o.date<i.dt)
  ;
quit;

Or given your logic - if I got this right - even something as simple as below should work.

proc sql;
  select max(dt) into :max_dt trimmed
  from input_dates
  ;
quit;

data a11;
  set sashelp.air(where=(date<&max_dt));
run;

[Ivan555]

@Patrick 

thank you much for response, but i have to go..

i will study it and give you feedback on monday, sorry. 😞

[Ivan555]

@Patrick 

Thank you much, that is close but seems not exactly what I wanted to get.

On both your scripts I am getting 18 rows in a11, but i wish to get only 2 rows:

> DEC49, which is being got on JAN50 and

> JUN50, which is being got on JUL50

[Patrick]

---

@Ivan555 wrote:

@Patrick 

Thank you much, that is close but seems not exactly what I wanted to get.

On both your scripts I am getting 18 rows in a11, but i wish to get only 2 rows:

> DEC49, which is being got on JAN50 and

> JUN50, which is being got on JUL50

---

@Ivan555 

That's because I apparently don't understand the description of the logic you want to implement - especially below bit in red. 

at start i do have
> a number of dates (input_dates), in example their quantity is 2, but in life it can vary.
> a table (sashelp.airline)
> and a condition on the table: to take most actual date < each input_date  according with the table - this is a result of A11.

[Ivan555]

@Patrick 

> being writing "date < each input_date" I will probably mean a list of dates < each input parameter
> being writing "most actual date < each input_date" I will probably mean the most actual date in a list of dates <  each input parameter

Sorry, perhaps my English is also not enough good too.. 😞