topic MACRO - USING A COLUMN FROM A TABLE AS FILTER IN A PROC SQL in SAS Programming

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

ph10 — Mon, 05 Aug 2024 20:07:26 GMT

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

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

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

Tom — Mon, 05 Aug 2024 20:21:21 GMT

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.

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

ph10 — Mon, 05 Aug 2024 20:30:08 GMT

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

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

SASKiwi — Mon, 05 Aug 2024 22:20:31 GMT

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;

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

Tom — Mon, 05 Aug 2024 23:03:54 GMT

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;

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

Taojin — Tue, 06 Aug 2024 05:43:04 GMT

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),')'));

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

Kurt_Bremser — Tue, 06 Aug 2024 08:20:44 GMT

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.