topic Re: Merging in SQL, want to write this code in sql: if (ab and mn) = 1 then flag = "Y"; el in SAS Programming

Merging in SQL, want to write this code in sql: if (a and b) = 1 then flag = "Y"; else flag = "N";

ManoharNath — Mon, 28 Mar 2022 15:54:30 GMT


data ab;
input id name$;
cards;
1 A
2 B
3 C
4 D
9 Z
;
RUN;

data mn;
input id name$;
cards;
1 A
2 B
3 C
4 D
4 N
5 E
6 F 
;
RUN;

/* please help me to write below code in sql, its little important*/
DATA TEST;
MEREG ab(in=a) mn(in=b);
by id;
if (a and b) = 1 then flag = "Y"; 
else flag = "N";
run;
proc print data = test;run;

Re: Merging in SQL, want to write this code in sql: if (ab and mn) = 1 then flag = "Y"; el

ballardw — Mon, 28 Mar 2022 14:36:27 GMT

First comment:

if (a and b) = 1 then

is basically identical to (in this case with exactly two data sets you can't ever have the case of a=0 and b=0 )

if a and b then

You will have to show the rest of your proposed SQL as what you request seriously depends on the remainder of the code.

Last, you may want to consider use of 1/0 numeric coded variables instead of character 'Y' and 'N'.

For one thing your flag code could be reduced to:

Flag = (a and b);

no "else" needed.

There are lots of ways to get counts and information about 1/0 coded values in summary that require much more work with 'Y' and 'N'. Such as Sum of Flag is the number of FLAG=1, mean is the percent of Flag=1 as a decimal (.5 = 50% displayed with a percent format).

Also you can assign a custom format to show 'Y' or 'Yes' or 'Flagged for xxxx' to make the value more readable of if desired. (You can still do the format with character values, just mean there is no reason to use character if you want to be human readable)

Re: Merging in SQL, want to write this code in sql: if (ab and mn) = 1 then flag = "Y"; el

ErikLund_Jensen — Mon, 28 Mar 2022 14:59:57 GMT

Hi @ManoharNath

The code supplied below does exactly the same as your data step, it detects matches on ID only, so 4 D and 4 N are both flagged as "Y", even if 4 N is only found in one table.

If that's all right then use the code as is, otherwise use the ON-clause in comments instead. And may I know why you want to do this in Proc SQL, when you have a working solution in a Data Step?


data ab;
input id name$;
cards;
1 A
2 B
3 C
4 D
9 Z
;
RUN;

data mn;
input id name$;
cards;
1 A
2 B
3 C
4 D
4 N
5 E
6 F 
;
RUN;

proc sql;
  create table abmn as
    select 
      coalesce(ab.ID, mn.ID) as ID,
      coalesce(ab.Name, mn.Name) as Name,
      ifc(ab.ID ne . and mn.ID ne .,'Y','N') as flag 
    from ab full outer join mn 
    on ab.ID = mn.ID;
 /* on ab.ID = mn.ID and ab.Name = mn.Name; */
quit;

Re: Merging in SQL, want to write this code in sql: if (ab and mn) = 1 then flag = "Y"; el

ManoharNath — Mon, 28 Mar 2022 16:01:58 GMT

Thank you, its working and giving right output.