topic Re: Proc sql : add the rows in SAS Programming

Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 16:59:09 GMT

Hello Experts,

I would like to join the rows with only the b.IS_KEY that are not in table a, how to rewrite this code :

proc sql;
	create table Tab_complet as select a.*, b.* from Tab2
		left join HISTO as b on a.NO_1 =b.NO_1 and
 a.IS_key=b.IS_key and a.D_VAL=b.D_VAL;
quit;

I would like to add to the table a the missing IS_key from table b, or, with proc sort, I would like to do this :

data tab_complet;
	merge Tab2(in=IN1) HISTO(in=IN2);
	by NO_1 IS_key D_VAL;
	if NO_1 (IN1=1 and IN2=1)  and IS_key (IN1=0 and IN2=1) and  D_VAL (IN1=1 and IN2=1);
run;

I other words, I would like to merge tab1+tab2 by X1 not eq X2 and X3 to have the result the tab3 :


data tab1;
input X1 X2 X3;
cards;
1 3 8
2 3 7
3 4 6
4 5 7
;
run;

data tab2;
input X1 X2 X3;
cards;
1 3 8
2 5 7
3 6 7
4 5 7
;
run;


data tab3;
input X1 X2 X3;
cards;
1 3 8
2 3 7
2 5 7
3 4 6
4 5 7
;
run;

Re: Proc sql : add the rows

PaigeMiller — Thu, 02 Nov 2023 14:20:09 GMT

Please explain what is wrong with the code you show. Please show us examples of your data sets (these could be totally fake data as long as the variable names and organization of the data is the same). Show us the data as WORKING data step code (examples and instructions), which was done by someone else in your other thread. Do not provide data in any other format.

Re: Proc sql : add the rows

A_Kh — Thu, 02 Nov 2023 14:20:29 GMT

If you have IS_KEY variable in both tables, and wanted to copy values from Table B to Table A (where missing) then COALESCE function works for you. Something like this should work.

proc sql;
	create table Tab_complet as 
               select a.*, b.* , coalesce(a.IS_KEY, b.IS_KEY) 
                     from Tab2 as a
		            left join 
                      HISTO as b 
                 on a.NO_1 =b.NO_1
         and  a.D_VAL=b.D_VAL;
quit;

Re: Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 14:40:58 GMT

Thank you, A_Kh, this is what I want but the result of your code is a full join. I would like to add the missing IS_SUPPORT values.

Re: Proc sql : add the rows

A_Kh — Thu, 02 Nov 2023 15:10:21 GMT

Without data I could only guess, and my guess is in your SELECT clause you don't need b.*. If you need only IS_SUPPORT values from Table B then try to join only this into Table A.
What if you try this:

proc sql;
	create table Tab_complet (drop n1 dval) as 
               select a.*, coalesce(a.IS_KEY, b.IS_KEY) as NEW_KEY
                     from Tab2 as a
		            left join 
                      (select IS_KEY, NO_1 as n1, D_VAL as dval from HISTO) as b 
                 on a.NO_1 =b.n1
         and a.D_VAL=b.dval;
quit;

Re: Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 15:42:40 GMT

Sorry, the IS_KEY are not missing, it's not ".". I would like to add the rows (by NO_POLICE and D_VAL) to table a if for this rows I have the different IS_KEY in table b. I would like to do this kind of join :

data tab_complet;
	merge Tab2(in=IN1) HISTO(in=IN2);
	by NO_1 IS_key D_VAL;
	if NO_1 (IN1=1 and IN2=1)  and IS_key (IN1=0 and IN2=1) and  D_VAL (IN1=1 and IN2=1);
run;

Re: Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 16:59:27 GMT

Thank you, I updated my message.

Re: Proc sql : add the rows

Tom — Thu, 02 Nov 2023 17:32:19 GMT

I do not understand how you got TAB3 from from TAB1 and TAB2.

You have all of the observations from TAB1 but have added only one of the observations from TAB2.

Two of the observations you eliminated appear to be exact duplicates of existing observations in TAB1.

But why did you add only one of the two new observations?

Re: Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 17:38:00 GMT

Thank you for your message. The line with data 2 5 7 doesn’t exist in table 1. This line match by x1 and x3 so I added this line.

Re: Proc sql : add the rows

Tom — Thu, 02 Nov 2023 18:22:14 GMT

So union TAB1 with the records from TAB2 that match TAB1 values of X1 and X3.

proc sql;
 create table want as 
 select * from tab1
 union
 select tab2.* from tab2 inner join tab1 on tab1.x1=tab2.x1 and tab1.x3=tab2.x3
 ;
quit;

Re: Proc sql : add the rows

SASdevAnneMarie — Thu, 02 Nov 2023 20:57:00 GMT

Thank you. Unfortunately, my real data is not the same, the columns are different, I can't do union. I have onle the same 3 keys for join. Actually, I would like to this :

data tab_complet;
	merge Tab2(in=IN1) HISTO(in=IN2);
	by NO_1 IS_key D_VAL;
	if NO_1 (IN1=1 and IN2=1)  and IS_key (IN1=0 and IN2=1) and  D_VAL (IN1=1 and IN2=1);
run;

Re: Proc sql : add the rows

Tom — Thu, 02 Nov 2023 22:33:50 GMT

Not sure I understand. How can you add observations if the datasets have different variables? That are you putting into the new observations in that case?

Provide a clearer example.

Perhaps you just need to make the subset of the second dataset first before trying to append/merge it with the first dataset?