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.
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.
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.
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.
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
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.
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.
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.
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.
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 16. 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.