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!
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
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;
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)
"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.
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..
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
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;
thank you much for response, but i have to go..
i will study it and give you feedback on monday, sorry. 😞
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 wrote:
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
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.
> 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.. 😞
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.