Unable to show results when there is a condition to check the Dataset name

[michael_sison]

Hi guys.

I'm having issue with showing the records that satisfy the condition of comparing the value in a column to a dataset name.

The code below is the Dataline that creates the "information" table:

/***************************************************************************************/

data CTL_JOINFLD;
infile datalines dsd /*delimiter=','*/;
length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.;
input tbl_name1 $ tbl_name2 $ id $ prim_key $;
datalines;
COUNTERPARTY,COUNTERPARTY_HIST,Y,COUNTERPARTY_ID
COUNTERPARTY,COUNTERPARTY_HIST,N,COUNTERPARTY_NAME
LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,Y,GL_ID
LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,N,BUSINESS_UNIT
;
run;

/***************************************************************************************/

The code below is to create macro function that set dataset1 and dataset2 macro variables to table names. 

I have other steps in the macro function but those aren't connected to this specific problem.

/***************************************************************************************/

%macro dynamic_left_join(dataset1,dataset2);

data UNQ_KEY;
  set CTL_JOINFLD; /*Used the table created above*/
  if tbl_name1 = "&dataset1" and tbl_name2= "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key);       

                      /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name*/

                     /*Also created new variable for later use*/
  else delete;  /*delete other records if not satisfy the condition*/
run;

%mend dynamic_left_join;

%dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);

/***************************************************************************************/

I also used PROC SQL STEP but showing no results too.

proc sql;
  create table UNQ_KEY as
  select tbl_name1, id, prim_key, catx(' = ','X.' || prim_key, 'Y.' || prim_key) as join_key
  from CTL_JOINFLD
  where (upcase(strip(tbl_name1)) = upcase(strip("&dataset1")) and upcase(strip(tbl_name2)) = upcase(strip("&dataset2")))

  /*Tried here to strip and put both values to Uppercase but still no results found*/
;
quit;

The result should show the records of Counterparty, Counterparty_Hist rows. But do not show after running.

Is there something wrong with comparing the value from a column with macro variable? Or what should be the proper solution?

Thanks in advance.

[ballardw]

Is there a reason that you are using the || operator in the middle of CATX code?

I am not clear what you expect as a result.

When I run  your code with that example data set I get two observations where tbl_name1=COUNTERPARTY and table_name2=COUNTERPARTY_HIST with values for Join_key added. Is there something else I am supposed to get?

If your data step version of the data set UNQ_KEY does not have any observations then set options mprint before running that macro. Copy the log created when the macro runs, on the forum open a text box with the </> and paste the resulting log showing the generated text and results.

options mprint;

%dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);

options nomprint;

The option Mprint will cause the generated code to appear in the log along with the other SAS code results messages. If any warnings or errors appear they will be in closer proximity to the statements with problems.

[michael_sison]

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         data CTL_JOINFLD;
 70         infile datalines dsd /*delimiter=','*/;
 71         length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.;
 72         input tbl_name1 $ tbl_name2 $ id $ prim_key $;
 73         datalines;
 
 NOTE: LOST CARD.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 
 79  CHAR   .;                                                                              
     ZONE   03222222222222222222222222222222222222222222222222222222222222222222222222222222
     NUMR   9B000000000000000000000000000000000000000000000000000000000000000000000000000000
 tbl_name1=  tbl_name2=  id=  prim_key=  _ERROR_=1 _N_=5
 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
 NOTE: The data set WORK.CTL_JOINFLD has 4 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              668.87k
       OS Memory           22436.00k
       Timestamp           08/16/2024 02:24:34 AM
       Step Count                        38  Switch Count  2
       Page Faults                       0
       Page Reclaims                     123
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         ;
 80         run;
 81         
 82         %macro dynamic_left_join(dataset1,dataset2);
 83         
 84         data UNQ_KEY;
 85           set CTL_JOINFLD; /*Used the table created above*/
 86           if tbl_name1 = "&dataset1" and tbl_name2= "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key);
 87         
 88                               /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name*/
 89         
 90                              /*Also created new variable for later use*/
 91           else delete;  /*delete other records if not satisfy the condition*/
 92         run;
 93         
 94         %mend dynamic_left_join;
 95         
 96         options mprint;
 97         
 98         %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);
 MPRINT(DYNAMIC_LEFT_JOIN):   data UNQ_KEY;
 MPRINT(DYNAMIC_LEFT_JOIN):   set CTL_JOINFLD;
 MPRINT(DYNAMIC_LEFT_JOIN):   if tbl_name1 = "COUNTERPARTY" and tbl_name2= "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || 
 prim_key, 'Y.' || prim_key);
 MPRINT(DYNAMIC_LEFT_JOIN):   else delete;
 MPRINT(DYNAMIC_LEFT_JOIN):   run;
 
 NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD.
 NOTE: The data set WORK.UNQ_KEY has 0 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              950.59k
       OS Memory           22952.00k
       Timestamp           08/16/2024 02:24:34 AM
       Step Count                        39  Switch Count  2
       Page Faults                       0
       Page Reclaims                     156
       Page Swaps                        0
       Voluntary Context Switches        14
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 99         
 100        options mprint;
 101        
 102        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 112        

Hi @ballardw,

For the || operator, I simply wanted to concatenate 'X.' and 'Y.' with the values in prim_key field of CTL_JOINFLD table.

e.g. DM_COUNTERPARTY,DM_COUNTERPARTY_HIST,Y,COUNTERPARTY_ID /*This is the first record from the CTL table*/

prim_key = COUNTERPARTY_ID

The catx code should generate like this:

X.COUNTERPARTY_ID = Y.COUNTERPARTY_ID 

I inserted the logs from the run and put the options mprint;.

Btw, I'm using SAS ODA. Is there some difference with what you are using?  Since you mentioned that you tried to run and there's observations. 

[ballardw]

The LOSTCARDS warning is that something went a bit wonky with reading your data lines.

It may be as simple as an extra line starting with a tab character. Did your verify that the data set looked correct before running the macro? If one line of your datalines acquired extra characters then others may have and the results for the data step might have an extra character at the beginning of one of the variables and the results would not match the macro variables.

Some of these results would not be visible with the table viewer or even proc print output as the defaults for both do not display leading white space characters or unprintable characters. Such characters may have disappeared when I copied from the forum and pasted the text into my editor. I am running in a local install of Display Manager (or Foundation or Base SAS) version 9.4.7.

You might try inserting these lines into the data step version of your macro immediately before the

" IF tbl_name1= " line of code.

length t1 t2 $ 30;
t1 = quote(tbl_name1);
t2 = quote(tbl_name2);
put t1= t2= ;

The LOG will show each value of t1 and t2. If one of the tbl_name variables has an odd character or two in it then the quotes may show something like " COUNTERPARTY" (leading white space) or possibly and unpritable character indicator instead of "COUNTERPARTY"

If you see such you may want to retype the start of each line of your datalines.

When I run your code for the data step and the macro copies straight from the forum and run it my log shows:

17   data CTL_JOINFLD;
18   infile datalines dsd /*delimiter=','*/;
19   length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.;
20   input tbl_name1 $ tbl_name2 $ id $ prim_key $;
21   datalines;

NOTE: The data set USER.CTL_JOINFLD has 4 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


26   ;
27   run;

28   %macro dynamic_left_join(dataset1,dataset2);
29
30   data UNQ_KEY;
31     set CTL_JOINFLD; /*Used the table created above*/
32     if tbl_name1 = "&dataset1" and tbl_name2= "&dataset2" then join_key = catx(' = ','X.' ||
32 ! prim_key, 'Y.' || prim_key);
33
34                         /*Set the condition of checking the value of tbl_name1 if it is equal
34 ! to the dataset name*/
35
36                        /*Also created new variable for later use*/
37     else delete;  /*delete other records if not satisfy the condition*/
38   run;
39
40
41
42   %mend dynamic_left_join;
NOTE: The macro DYNAMIC_LEFT_JOIN completed compilation without errors.
      11 instructions 432 bytes.
43
44
45
46   %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);

NOTE: There were 4 observations read from the data set USER.CTL_JOINFLD.
NOTE: The data set USER.UNQ_KEY has 2 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Note that I don't get the lostcard warning or diagnostics.

And the result for join_key:

47   ods listing;
48   proc print data=unq_key;
49      var join_key;
50   run;

NOTE: There were 2 observations read from the data set USER.UNQ_KEY.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time

The output is:

--------------------------------------------------------------------------------------------------

Obs                    join_key

 1     X.COUNTERPARTY_ID = Y.COUNTERPARTY_ID
 2     X.COUNTERPARTY_NAME = Y.COUNTERPARTY_NAME

[michael_sison]

Hi @ballardw ,

For the LOSTCARDS warning, I just mistakenly entered a new line after the 4th record in my datalines step. Adter removing the new line, no more lostcard warning found.

For the tbl_name1 and tbl_name2, I found out that there are trailing spaces after Counterparty and Counterpart_hist. You can check the logs that I inserted.

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         data CTL_JOINFLD;
 70         infile datalines dsd /*delimiter=','*/;
 71         length tbl_name1 $25. tbl_name2 $25. id $20. prim_key $20.;
 72         input tbl_name1 $ tbl_name2 $ id $ prim_key $;
 73         datalines;
 
 NOTE: The data set WORK.CTL_JOINFLD has 4 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              667.43k
       OS Memory           19876.00k
       Timestamp           08/16/2024 04:07:39 PM
       Step Count                        31  Switch Count  2
       Page Faults                       0
       Page Reclaims                     125
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 78         ;
 79         run;
 80         
 81         %macro dynamic_left_join(dataset1,dataset2);
 82         
 83         
 84         data UNQ_KEY;
 85           set CTL_JOINFLD; /*Used the table created above*/
 86           length t1 t2 $ 100;
 87           t1 = QUOTE(tbl_name1);
 88           t2 = QUOTE(tbl_name2);
 89           put t1= t2= ;
 90           if tbl_name1 = "&dataset1" and tbl_name2 = "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key);
 91                               /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name
 92                              Also created new variable for later use*/
 93           else delete;  /*delete other records if not satisfy the condition*/
 94         run;
 95         
 96         %mend dynamic_left_join;
 97         
 98         
 99         options mprint;
 100        
 101        %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);
 MPRINT(DYNAMIC_LEFT_JOIN):   data UNQ_KEY;
 MPRINT(DYNAMIC_LEFT_JOIN):   set CTL_JOINFLD;
 MPRINT(DYNAMIC_LEFT_JOIN):   length t1 t2 $ 100;
 MPRINT(DYNAMIC_LEFT_JOIN):   t1 = QUOTE(tbl_name1);
 MPRINT(DYNAMIC_LEFT_JOIN):   t2 = QUOTE(tbl_name2);
 MPRINT(DYNAMIC_LEFT_JOIN):   put t1= t2= ;
 MPRINT(DYNAMIC_LEFT_JOIN):   if tbl_name1 = "COUNTERPARTY" and tbl_name2 = "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || 
 prim_key, 'Y.' || prim_key);
 MPRINT(DYNAMIC_LEFT_JOIN):   else delete;
 MPRINT(DYNAMIC_LEFT_JOIN):   run;
 
 t1="COUNTERPARTY            " t2="COUNTERPARTY_HIST        "
 t1="COUNTERPARTY            " t2="COUNTERPARTY_HIST        "
 t1="LKP_BS_GL_FRP_MAP       " t2="LKP_BS_GL_FRP_MAP        "
 t1="LKP_BS_GL_FRP_MAP       " t2="LKP_BS_GL_FRP_MAP        "
 NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD.
 NOTE: The data set WORK.UNQ_KEY has 0 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              959.31k
       OS Memory           20136.00k
       Timestamp           08/16/2024 04:07:39 PM
       Step Count                        32  Switch Count  2
       Page Faults                       0
       Page Reclaims                     136
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           272
       
 
 102        
 103        options mprint;
 104        
 105        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 115        

So, I revised the code to remove the trailing space like this:

data UNQ_KEY;
  set CTL_JOINFLD; /*Used the table created above*/
  length t1 t2 $ 30;
  t1 = strip(tbl_name1);
  t2 = strip(tbl_name2);
  put t1=t2=;
  if t1 eq "&dataset1" and t2 eq "&dataset2" then join_key = catx(' = ','X.' || prim_key, 'Y.' || prim_key);       
                      /*Set the condition of checking the value of tbl_name1 if it is equal to the dataset name
                     Also created new variable for later use*/
  else delete;  /*delete other records if not satisfy the condition*/
run;

%mend dynamic_left_join;

But still I'm getting 0 results. I checked that t1 and t2 do not anymore have trailing spaces. 

 99         options mprint;
 100        
 101        %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);
 MPRINT(DYNAMIC_LEFT_JOIN):   data UNQ_KEY;
 MPRINT(DYNAMIC_LEFT_JOIN):   set CTL_JOINFLD;
 MPRINT(DYNAMIC_LEFT_JOIN):   length t1 t2 $ 30;
 MPRINT(DYNAMIC_LEFT_JOIN):   t1 = strip(tbl_name1);
 MPRINT(DYNAMIC_LEFT_JOIN):   t2 = strip(tbl_name2);
 MPRINT(DYNAMIC_LEFT_JOIN):   put t1=t2=;
 MPRINT(DYNAMIC_LEFT_JOIN):   if t1 eq "COUNTERPARTY" and t2 eq "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 
 'Y.' || prim_key);
 MPRINT(DYNAMIC_LEFT_JOIN):   else delete;
 MPRINT(DYNAMIC_LEFT_JOIN):   run;
 
 t1=COUNTERPARTY t2=COUNTERPARTY_HIST
 t1=COUNTERPARTY t2=COUNTERPARTY_HIST
 t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP
 t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP
 NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD.
 NOTE: The data set WORK.UNQ_KEY has 0 observations and 7 variables.

Thank you for the tips on checking the table name. I think I'm getting near to the right solution.

[Tom]

Using STRIP() with an assignment statement adds nothing over using LEFT().  The trailing spaces it removed will just be added back when the value is saved into the fixed length character variable.

But it might help to use TRIM() with your code that is making the quoted version. That way you might be able to see if those trailing spaces are actually spaces or some other invisible character.

t1 = QUOTE(trim(tbl_name1));

Perhaps you have other tab characters in your actual data.  Or perhaps CR characters.

[Tom · Accepted solution]

Something is wrong with your datalines.

Copying your code from your original post and it works fine for me.

One thing that looks very strange is this TAB character in the first column of the last line of the data that it echoed to the LOG with the error message.

Where did that come from?

Always when entering in-line data in the SAS editor make sure to start the lines in column one.  Do NOT let SAS autoindent the lines.  To remind yourself you should remove the indentation from the DATALINES (or CARDS) statement also.

Here is your data step cleaned up.  I also removed the unneeded periods from the LENGTH statement and the unneeded $ from the INPUT statement (the types of the variables has already been set by the LENGTH statement.)

data CTL_JOINFLD;
  infile datalines dsd /*delimiter=','*/;
  length tbl_name1 $25 tbl_name2 $25 id $20 prim_key $20;
  input tbl_name1 tbl_name2 id prim_key ;
datalines;
COUNTERPARTY,COUNTERPARTY_HIST,Y,COUNTERPARTY_ID
COUNTERPARTY,COUNTERPARTY_HIST,N,COUNTERPARTY_NAME
LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,Y,GL_ID
LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,N,BUSINESS_UNIT
;

NOTE:  You can change your Editor preferences so that uses SPACES instead of physical TAB characters when it indents code lines.

[michael_sison]

Hi Tom,

Thank you. Finally, it works. It seems the tab character for indention is causing the issue. There are now observations found. 

100        %dynamic_left_join(COUNTERPARTY, COUNTERPARTY_HIST);
 MPRINT(DYNAMIC_LEFT_JOIN):   data UNQ_KEY;
 MPRINT(DYNAMIC_LEFT_JOIN):   set CTL_JOINFLD;
 MPRINT(DYNAMIC_LEFT_JOIN):   length t1 t2 $ 30;
 MPRINT(DYNAMIC_LEFT_JOIN):   t1 = trim(tbl_name1);
 MPRINT(DYNAMIC_LEFT_JOIN):   t2 = trim(tbl_name2);
 MPRINT(DYNAMIC_LEFT_JOIN):   put t1=t2=;
 MPRINT(DYNAMIC_LEFT_JOIN):   if t1 eq "COUNTERPARTY" and t2 eq "COUNTERPARTY_HIST" then join_key = catx(' = ','X.' || prim_key, 
 'Y.' || prim_key);
 MPRINT(DYNAMIC_LEFT_JOIN):   else delete;
 MPRINT(DYNAMIC_LEFT_JOIN):   run;
 
 t1=COUNTERPARTY t2=COUNTERPARTY_HIST
 t1=COUNTERPARTY t2=COUNTERPARTY_HIST
 t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP
 t1=LKP_BS_GL_FRP_MAP t2=LKP_BS_GL_FRP_MAP
 NOTE: There were 4 observations read from the data set WORK.CTL_JOINFLD.
 NOTE: The data set WORK.UNQ_KEY has 2 observations and 7 variables.