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;
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;
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)
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;
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 25. 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.