topic Re: Learning PROC SQL Equivalent in SAS Programming

Learning PROC SQL Equivalent

SASLearner7 — Thu, 04 Apr 2019 15:34:28 GMT

I am trying to learn how to use PROC SQL. I am trying to figure out how to completely use PROC SQL to do the same logic and have the same output dataset as the code below. Pets1 and Pets2 datasets are both really wide and long so I try to subset the data before merging. I'm hoping I can accomplish the same using purely PROC SQL.

proc sql noprint;

create table temp1 AS

select a, b, c

from pets1

order by a, b;

create table temp2 AS

select a, b

from pets2

order by a, b;

quit;

data A3;

merge temp1 (in=A) temp2 (in=B);

by a b;

length s $10;

if A and B

then s= 'both';

else if A

then s= 'temp1';

else if B

then s= 'temp2';

run;

Thank you.

Re: Learning PROC SQL Equivalent

novinosrin — Thu, 04 Apr 2019 15:38:29 GMT

Could you post sample data for us to work with plz?

Re: Learning PROC SQL Equivalent

ballardw — Thu, 04 Apr 2019 16:08:49 GMT

@SASLearner7 wrote:

I am trying to learn how to use PROC SQL. I am trying to figure out how to completely use PROC SQL to do the same logic and have the same output dataset as the code below. Pets1 and Pets2 datasets are both really wide and long so I try to subset the data before merging. I'm hoping I can accomplish the same using purely PROC SQL.

proc sql noprint;

   create table temp1 AS

      select a, b, c

      from pets1

      order by a, b;

   create table temp2 AS

      select a, b

      from pets2

      order by a, b;

quit;

data A3;

   merge temp1 (in=A) temp2 (in=B);

   by a b;

   length s $10;

   if A and B

      then s= 'both';

   else if A

      then s= 'temp1';

   else if B

      then s= 'temp2';

run;

Thank you.

Example data.

Describe what the actual intent of your code is. Without data I can't tell what your result actually may be.

And there are somethings the data step does that are much more complex (read possibly requiring either multiple temporary data sets or subqueries and complex joins) in Proc SQL and vice versa. Note that the way Proc SQL used variables with the same name that you need to explicitly write COALESCE,r COALESCEC or case statements code to do what Merge does.

For instance in your pets data sets are the values combinations of A and B variables repeated? Different or same numbers of repeats between the two sets?

Poor idea to use BY variables and name the IN= variables with the same name. Are you sure your data step works as intended?

Re: Learning PROC SQL Equivalent

SASLearner7 — Thu, 04 Apr 2019 16:11:51 GMT

Variables a and b are composite keys which are unique for each record for pets1 and pets2. I am trying to figure out how to do it within the confines of PROC SQL syntax since it's different from DATA STEP syntax. For my example, I am trying to figure out if any records in pets1 appears in pets2, and vice versa, or if they appear on datasets. Hopefully that makes sense.

Sample data:

data pets1;

a = 2015; b='boxer'; c='123xyz'; addr='a'; output;

a = 2016; b='cat'; c='5687abc'; addr='b'; output;

a = 2018; b='dog'; c='123abc'; addr='a'; output;

a = 2011; b='cat'; c='5687abc'; addr='b'; output;

run;

data pets2;

a = 2018; b='dog'; c='135abc'; addr='a'; output;

a = 2011; b='cat'; c='5837abc'; addr='b'; output;

a = 2015; b='fly'; c='1234xyz'; addr='a'; output;

a = 2016; b='cat'; c='5687abc'; addr='b'; output;

a = 2014; b='cat'; c='5837abc'; addr='b'; output;

a = 2015; b='fly'; c='1234xyz'; addr='a'; output;

run;

Re: Learning PROC SQL Equivalent

novinosrin — Thu, 04 Apr 2019 16:51:34 GMT

@SASLearner7 Proc sql isn't convenient but if that's what you want-->


/*Your samples*/
data pets1;

a = 2015; b='boxer'; c='123xyz'; addr='a'; output;

a = 2016; b='cat'; c='5687abc'; addr='b'; output;

a = 2018; b='dog'; c='123abc'; addr='a'; output;

a = 2011; b='cat'; c='5687abc'; addr='b'; output;

run;

data pets2;

a = 2018; b='dog'; c='135abc'; addr='a'; output;

a = 2011; b='cat'; c='5837abc'; addr='b'; output;

a = 2015; b='fly'; c='1234xyz'; addr='a'; output;

a = 2016; b='cat'; c='5687abc'; addr='b'; output;

a = 2014; b='cat'; c='5837abc'; addr='b'; output;

a = 2015; b='fly'; c='1234xyz'; addr='a'; output;

run;

/*Using Sort and Merge*/
proc sort data=pets1;
by a b;
run;

proc sort data=pets2;
by a b;
run;

data A3;

   merge pets1 (in=in1) pets2 (in=in2);

   by a b;

   length s $10;

   if in1 and in2

      then s= 'both';

   else if in1

      then s= 'pets1';

   else if in2

      then s= 'pets2';

run;

/*Using Proc sql*/

proc sql;
create table a3__ as
select coalesce(a.a,b.a) as a,coalesce(a.b,b.b) as b,coalesce(a.c,b.c) as c, coalesce(a.addr,b.addr) as addr,
case when a.a=b.a and a.b=b.b   then  'both' 
when a.a>. then 'Pets1'
when b.a>. then 'pets2' else ' ' end as s
from pets1 a full join pets2 b
on a.a=b.a and a.b=b.b;
quit;