topic Re: Merge and proc sql in SAS Procedures

Merge and proc sql

Pooja98 — Mon, 06 Jan 2020 11:14:06 GMT

Difference between merge and proc sql

Re: Merge and proc sql

ed_sas_member — Mon, 06 Jan 2020 11:44:30 GMT

Hi @Pooja98

Both DATA step merges and PROC SQL joins enable to join two or more tables.

Sometimes they can produce the same outputs.

Here are some of the main differences:

- contrary to DATA step merges, you don't need to sort your data when you use PROC SQL joins

- you need to add a "create table xxx as" clause in the PROC SQL as the default output is a report

- common columns need to have the same name in a DATA step merge, which is not the case for PROC SQL.

- PROC SQL does not overlay common columns by default -> you need to mention one explicitly in the select clause or use the COALESCE option

- Another thing is that you can use another operator that = in you joint expression.

- in case your input datasets have not a unique "key" for merging, it is useful to use PROC SQL as by design, it will create the Cartesian product of input datasets as the result and then delete observations that don't meet the join expression condition(s) -> so depending on the method you choose, you can have different results.

PROC SQL can be also more efficient, especially if you have large datasets.

Hope this help!

Best,

Re: Merge and proc sql

Pooja98 — Mon, 06 Jan 2020 12:53:19 GMT

Thanks a lot!!

Re: Merge and proc sql

Pooja98 — Mon, 06 Jan 2020 12:53:43 GMT

What is %include statement??

Re: Merge and proc sql

ed_sas_member — Mon, 06 Jan 2020 12:55:54 GMT

Hi @Pooja98

The %include statement allow you to bring a SAS program stored in an external file in your current program.

Here is the documentation about this statement: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000214504.htm

Re: Merge and proc sql

ChrisHemedinger — Mon, 06 Jan 2020 12:58:39 GMT

Long-time SAS user and community member @Rick_Andrews summarizes the differences and techniques in this article:

Traditional SAS Programming versus PROC SQL

Re: Merge and proc sql

Pooja98 — Mon, 06 Jan 2020 13:21:59 GMT

Hi, can anyone help me with this???
I need to remove duplicates and at the same time i don't want to change my order

Name

Ram
Seetha
Radha
Bheem
Arjun
Seetha
Raj
Ram

The output should be :

Ram
Seetha
Radha
Bheem
Arjun
Raj

I tried both nodupkey and distinct, i got the results but name variable displaying in ascending order

Re: Merge and proc sql

unison — Mon, 06 Jan 2020 14:11:53 GMT

Try this

data have;
	input name :$10.;
	datalines;
Ram
Seetha
Radha
Bheem
Arjun
Seetha
Raj
Ram
;
run;

data desired_output;
	input name :$10.;
	datalines;
Ram
Seetha
Radha
Bheem
Arjun
Raj
;
run;

data have_with_rownum;
	set have;
	rownum=_n_;
run;

proc sort data=have_with_rownum nodupkey out=dedup;
	by name;
run;

proc sort data=dedup out=want(keep=name);
	by rownum;
run;

/*Check that want=desired_output*/
proc compare base=desired_output compare=want;
run;

Re: Merge and proc sql

Pooja98 — Tue, 07 Jan 2020 13:30:50 GMT