Much has already been written on the topic. A nice intro can be found at: http://www2.sas.com/proceedings/sugi30/249-30.pdf
However, a web search for sas sql merge will bring up quite a few more papers that explain even more of the differences and similarities
Try the following example. You will discover that the datastep merge will have the same result as a sql join:
input sex $ amount;
proc sort data=second;
merge first second;
However, a many-to-many merge is more difficult to achieve via a datastep merge.
The same thing is true for a datastep merge.
Rename most certainly handles a scenario when your matching columns have different names (but have the same content).
But SQL lets you join multiple tables with differern join criteria in the different "join pairs".
I.e. you can join table A and B on ssn, and table B and C on Zip-code.I think this is not possible in a MERGE step.
this two are correct or not. make sure
I feel that the main difference is in Merge, it includes numerous steps to merge the data like Sorting the data firstt by using a BY variable and then merge the data sets horizontallay using the same BY variable. But this is good for small amount of data.
If you have millions of records and u do want the same process then Proc Sql will be the option which saves your time and effort by creating simple code in a single step.
Correct me if i am wrong.
SQL does not care about incoming data order. This might be regarded as an advantage in many contexts (although it can generate a performance price). But unlike the MERGE (or other data step statements like SET), it provides no reliable (not to mention efficient) way to look ahead (or look back) from the observation in hand.
The advantages of MERGE are intrinsically the advantages of sequential processing. Say you have a dataset sorted by ID/DATE and you want, for each record, the number of days between the current date and the preceding and successive dates in a dataset, within each ID. Then a simple self-merge (with the firstobs=2) dataset name parameter for one of the merged data streams, can suffice.
Consider this sample of 500,000 ID's with about 5.8m observations, sorted by ID/DATE. The DATA WANT step uses MERGE (with a firstobs=2 parameter) to get the lookahead date (NEXT_DATE) and corresponding DAYS_UNTIL_NEXT_DATE, and LAG function to get PRIOR_DATE, and DAYS_SINCE_PRIOR_DATE.
It takes about 0.81 seconds to run on my machine. Neither the lookahead technique or the LAG function are available in PROC SQL.
data have; do id=1 to 500000; do date='01jan1990'd by 0 until (date>='01jan2020'd); output; date=date + ceil(1990*ranuni(0159855)); end; end; format date date9. ; run; data want (drop=next_id); merge have have (firstobs=2 rename=(id=next_id date=next_date)); if next_id^=id then call missing(next_date); if next_date^=. then days_to_next_date=next_date-date; prior_date=ifn(lag(id)=id,lag(date),.); if prior_date^=. then days_since_prior_date =date-prior_date; format prior_date date9. ; run;
The analogous PROC SQL below would look (to me) relatively ugly. And it takes 16.73 seconds - about 20 times as long:
proc sql; create table want_SQL as select L.*,R.next_date ,case when R.next_date^=. then R.next_date-date else . end as days_to_next_date ,R2.prior_date , case when R2.prior_date^=. then date-R2.prior_date else . end as days_since_prior_date from (select L.*,R.next_date from have as L left join have (rename=(date=next_date)) as R on R.id=L.id and next_date>date group by L.id,date having next_date=min(next_date) ) left join have (rename=(date=prior_date)) as R2 on L.id=R2.id and R2.prior_date<date group by L.id,date having prior_date=max(prior_date) ; quit;
But the above is done with a minimal memory burden for PROC SQL - there are only 2 variables in dataset HAVE. Adding other memory-consuming vars will change the performance ratio considerably. In fact, when I added 6 character variables (each with length $200), the MERGE approach took 15 seconds, but the PROC SQL blew up with insufficient memory after 8 minutes.
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.