Creating a loop in SAS Macro that compares a table with lots of others

[Debugger]

Hi guys,

I already opened two topics where I tried to describe my problem.

https://communities.sas.com/t5/SAS-Programming/ERROR-22-322-ERROR-76-322-Macro-Variable-amp-n-genera...

https://communities.sas.com/t5/SAS-Programming/Problems-doing-a-loop-in-SAS-Macro/m-p/625390#M184311

As I am a complete newbie to SAS I hope you forgive me that, I strugeled to describe what I actually wanted to achieve and therefore tried to break down my Problem to different steps.

Here the consolidated version of the problem:

I want to compare Table X which contains values that are part of other tables.
E.g. Does table X contain:

Person a which is part of Table A
Person z which is part of Table AX
Person ed which is part of Table SE
....

I now want to create a SAS Project, that automatically compares table X with its values in the respective other tables as there may be fields in table X that are not used anymore.

My Idea broken down in the different steps was:

1. Creating the macro variables. Which are &n the number of observations, &Joinfeld the field which is necessary to join the tables, &name the  name of the table Table X is referring to.

2. Joining Table X with the respective other tables so that I got table X and a potential empty or filled field from the other tables.

3. Creating a table that consolidates the joined fields in one table.

4. Creating a Macro that automizes step 2 and 3.

Here my code that I wrote and the respective problems in red:

1.

proc sql;
select count(*) into :n
from table X;
%let n=&n;
Quit;

proc SQL;
Select distinct NAME into :name1 -
from table x;
Quit;
Proc SQL;
select distinct Tabellenname into :Joinfeld1 -
from table X;
Quit;

Problem: How do I create &n, &name and &Joinfeld, so that they are enumerated, as in my opinion I need this to do the loop of in the macro !?
Is there any other way, that the macro can detect, that Number 1 of table X is done and goes on to number 2 and so forth?

2.

PROC SQL;
   CREATE TABLE Test AS 
   SELECT t1.Stichwort_ID1, 
          t1.Stichwort_TXT1, 
          t1.Ausprägung, 
          t1.TXT, 
          t1.NAME, 
          t1.Tabellenname, 
          t2.&Joinfeld1
      FROM WORK.table X
           Left join &name1 t2 ON (t1.Ausprägung = t2.&Joinfeld1)
     Where t1.Ausprägung not= t2.&Joinfeld1;

QUIT;

3.

How can I create a Table, that keeps the steps in Number 2. and consolidates them into one?

4.

%Macro Loop;
%Do i=1 %to &n;

%End;
%Mend Loop;
%Loop;

Thanks in advance.

[Kurt_Bremser]

FROM WORK.table X
           Left join &name1 t2 ON (t1.Ausprägung = t2.&Joinfeld1)
     Where t1.Ausprägung not= t2.&Joinfeld1;

This can only result in zero observations selected.

As you are a complete newbie, I STRONGLY suggest you deal with simpler issues first before engaging in something this difficult.

Start with giving us an example for your table X, and some examples for the tables used dynamically, and what you then expect to get out of it.

Show us at least two variants of step 2 that you would want to run; also make sure that these variants actually achieve what you want. We can then determine what changes from one to another, and how to make it dynamic and automated.

Successful macro development starts with working SAS code.

[Debugger]

thanks for your reply @Kurt_Bremser :

Unfortunately I have to deal with this task and as well think it is really difficult.

To the first part of you question.

An Example of Table X would be Something like this.

Stichwort      TXT    Ausprägung    Name                          Tabellenname   

                               Joinfeld

A                  A        Person A          Name of Person A      Table A

A                  A        Person A2        Name of Person A      Table A

A                  A        Person A3        Name of Person A      Table A

z                   z         Person z           Name of Person z      Tabel z

z                   z         Person z2         Name of Person z      Tabel z

z                   z         Person z3        Name of Person z       Tabel z

ed                ed       Person ed        Name of Person ed    Table ed

ed               ed       Person ed2      Name of Person ed     Tabel ed

Table A would now contain

Person A

Person A2

Table z would contain

Person z

Person z2

Person z3

In a join of both tables I would like to find out now, that Table A is missing Person A3 one of the "Ausprägungen" of Table X and Table z would contain all of the "Ausprägungen".

[Debugger]

To the second part of the question:

I actually ran the sql Code I wanted to automize twice, with the following code (which is slightly different from the code I ran in the first place).

PROC SQL;
CREATE TABLE Test AS
SELECT distinct t1.Ausprägungen,
t2.Joinfeld A
FROM WORK.Table X
Left join Table A t2 ON (t1.Ausprägungen = t2.Joinfeld A)
WHERE t2.Joinfeld A NOT = '';
QUIT;

I actually ran this code and got the results i wanted:

Table X      Table A

Person A   Person A

Person A2 Person A2

Person A3 Person A3

I would now see the matching "Ausprägungen".

Instead of posting another example I highlighted the parts which I want to automatize.

I hope this gets clearer now.

Thanks for your help.

[Reeza]

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

If you have a working program, try following my tutorial on converting your program to a macro.

---

@Debugger wrote:

To the second part of the question:

I actually ran the sql Code I wanted to automize twice, with the following code (which is slightly different from the code I ran in the first place).

PROC SQL;
CREATE TABLE Test AS
SELECT distinct t1.Ausprägungen,
t2.Joinfeld A
FROM WORK.Table X
Left join Table A t2 ON (t1.Ausprägungen = t2.Joinfeld A)
WHERE t2.Joinfeld A NOT = '';
QUIT;

I actually ran this code and got the results i wanted:

Table X      Table A

Person A   Person A

Person A2 Person A2

Person A3 Person A3

I would now see the matching "Ausprägungen".

Instead of posting another example I highlighted the parts which I want to automatize.

I hope this gets clearer now.

Thanks for your help.

---

[Debugger]

Thanks for your reply @Reeza .

But unfortunately I think I already followed your Tutorial. As you can see in the posts before.

I did already test my sql outside the macro

I did replace every name I want to automatize with the macro.

I did transfer the variables into macro.

However my macro does not recognize the variables as a enumerated list you can loop through...

[Reeza]

Ok, show me three versions of the code, with the parmaeters changed and show the full code. Please use the code window to show your code (7th icon in the editor window) so it shows correctly.

[Tom]

Your WHERE clause does not look like valid syntax.

WHERE t2.Joinfeld ed NOT = '';

You have two variable names in a row. JOINFIELD from the table aliased by T2 and ED.

Your output table name is also not valid syntax.

CREATE TABLE Test 1 AS

Did you want to make a table named TEST1? or TEST_1?  If you really want to have a space in the middle of the table name then you need to use a name literal, 'TEST 1'n  or set option to allow SQL to treat double quotes as enclosing object names and use "TEST 1"

[Debugger]

thank for your reply i will edit the name

[Reeza]

---

@Debugger wrote:

However my macro does not recognize the variables as a enumerated list you can loop through...

---

You pulled the lists into a data set and used CALL EXECUTE as in the example? The reason I used this exact approach is because it's less error prone than the loops and easier for beginners to code. 

[Debugger]

How would the code look like as I have two variables that i will use in the macro?

Would I do the

*get list of all makes;
proc sql;
create table car_makes as 
select distinct make 
from sashelp.cars;
quit;

Step twice?

[Tom]

---

@Debugger wrote:

How would the code look like as I have two variables that i will use in the macro?

Would I do the

*get list of all makes;
proc sql;
create table car_makes as 
select distinct make 
from sashelp.cars;
quit;

Step twice?

---

Depends on what you want. 

Are you looking for all combinations of the two variables that exist in the data source? 

create table car_make_and_model as 
select distinct make,model 
from sashelp.cars;

[Reeza]

---

@Debugger wrote:

How would the code look like as I have two variables that i will use in the macro?

Would I do the

*get list of all makes;
proc sql;
create table car_makes as 
select distinct make 
from sashelp.cars;
quit;

Step twice?

---

The data set generated should be all combinations of what you want to pass to the macro. So for example if you wanted to run it for all origin, make combinations you would select both of those and either make a table that has all combinations or rely on the data depending on what you're doing.

So if you had a macro that you were running that took two parameters you could either call it multiple times manually or you can use a data step to create the macro call for you and then it will execute the command via CALL EXECUTE. 

*run a macro for all two way combinations of A, B, C;
data macroParams;
input param1 param2;
cards;
A B
A C
A D
B C
B D
C D
;;;;
run;

*example macro that does nothing;
%macro random_example(p1= , p2=);

%put Parameter1 = &p1;
%put Parameter2 = &p2.;

%mend;

*example manual call;
%random_example(p1=A, p2=B);


data demoRun;
set macroParams; *input data is all the combinations;

*the goal here is to make variable string look exactly like the command above and once it does, you pass that to call execute to run;
str = catt('%random_example(p1=', param1, ", p2=", param2, ");");

*this would actually execute the macro;
call execute(str);
run;
 

[Tom]

You need to show more clearly what you have. It sounds like you have metadata table that has both a the name of the DATASET and also the name of the variable to use as key.  So something like:

data master_list;
  input dsname :$41. keyvar :$32. ;
cards;
sashelp.class name
;

Then you want to use that data to generate code where at certain places the values of DSNAME and KEYVAR are used.

A good way to start is to make a macro that has two input parameters that match those names.

%macro runone(dsname,keyvar);
proc sql;
create table TEMP as
select .....
from &dsname A
inner join FIXED_TABLE B
on A.&keyvar = B.&keyvar
;
quit;
%mend ;

Try running that by passing in the names by hand.

%runone(sashelp.class,name);

When you get it working then use the dataset to generate the macro calls.

data _null_;
  set master_list;
  call execute(cats('%nrstr(%runone)(',dsname,',',keyvar,')'));
run;

Since the query you posted seems to be making datasets you will probably want to add a third parameter that tells the macro what dataset name to generate.  If you don't have that in your source metadata table you can just make one up in the data step that generates the calls.  Say by appending the value of the automatic iteration counter, _N_, to the end of a basename, like TEST. 

data _null_;
  set master_list;
  call execute(cats('%nrstr(%runone)(',dsname,',',keyvar,'TEST',_n_,')'));
run;

[Debugger]

@Reeza.

Okay okay thx.
Let us forget the data step you Just offered AS second solution.
I am already confused enough.
In the example you wanted me to read you are only definjng once the macro variable "Toyota". In my case a have a SQL(!!) Table Witz idk 137 distinct valuea which i want to be passed AS macro variables in a enumerated order. AS i specified in the beginning... Do you think your Case wohld Work Here?