MACRO - USING A COLUMN FROM A TABLE AS FILTER IN A PROC SQL

[ph10]

Helo, i have two tables as  describe below:

Tab_A - one columns called source with three rows.

Tab B - Five columns where one of them is the same as tab A.

Ex.

Tab A 

Source

A

B

C;

Tab B

Source          date                value

A             01.01.2024         10000

B             01.01.2024         11000

C             01.01.2024         12000

I want to loop through the table A to get the records from Table B where the Tab A values are  filters 

proc sql

select * from Tab B where source = 'A' 

then

select * from Tab B where source = 'B'  

then 

select * from Tab B where source = 'C' 

I would like to use ut in a macro

[Tom]

I do not understand why you want to run three queries.

Why not just use one query?

select * from TabB
 where source in (select source from TabA)
;

As to whether you need a macro or not and if so what type of macro you will need to describe what part of the code you would want to change with each different call to the macro.

[ph10]

I would like to create three tables with the name of the variable coming from tabA,
That's the reason to try to build it by macro.
For example:
Table A_source1 will come from TabB where Source = TabA, first record

Table A_source2 will come from TabB where Source = TabA, second record

Table A_source3 will come from TabB where Source = TabA, third record

[SASKiwi]

If you just want to stack your records then why not just use UNION?

proc sql;
  create table A as
  select * from B where source = 'A' 
  union all
  select * from B where source = 'B'  
  union all
  select * from B where source = 'C' ;
quit;

[Tom · Accepted solution]

Seems like a silly idea.

I would code it as a macro that take TWO inputs.  The value of SOURCE and the NAME of the output dataset.

%macro subset(source,out);
proc sql;
create table &out as select * from tabB where source = &source;
quit;
%mend subset;

You could then use the values from TABA to call the macro.  Unless you are positive the values of SOURCE can be used to make valid (unique) dataset names I would just number the dataset names.

data _null_;
  set tabA ;
  by source;
  if first.source;
  dsnum +1;
  call execute(cats('%nrstr(%subset)(',quote(trim(source,"'")),',',cats('tablea_source',dsnum)));
run;

[Taojin]

It looks like good, but one little comment. There is some questions when testing, so I modified to following code:
call execute(cats('%nrstr(%subset)(',quote(trim(source),"'"),',',cats('tablea_source',dsnum),')'));

[Kurt_Bremser]

Why do you think you need to split your data in the first place?

In almost all cases, splitting the data complicates further work with it.