SAS Tutorial | Merging Data Sets in SAS using SQL

[@deepakkumaar9665]

Mesmerizing art of teaching. Love from India 🇮🇳

[@m1cannas]

Super way to teach.

[@SASUsers]

Glad you liked it!

[@itsmev0]

Thank you, very helpful!

[@sumanlama6138]

Which technique (Data Step or proc sql) would you say is more efficient when joining very large ( millions/billions record with 1000s of column) tables?

[@SASUsers]

Suman, thank you for your question! We are checking on this for you and will follow up with a reply soon.

[@sumanlama6138]

@@SASUsers Thank you so much. I look forward to hearing back from you.

[@sumanlama6138]

@@annayarbrough7582 Thank you for the update. Tables are sorted and the output is also single table. If I understand correctly, Data Merge is the way to go if table is sorted else go with Proc SQL? I will definitely look into those options you mentioned. Thank you once again for the update.

[@sumanlama6138]

@@annayarbrough7582 Got it. Thank you !

[@gaoumblues9801]

The datastep. You have to be careful with the proc sql because if it needs to sorts data for you (yeah, it does that) then your performance my take a serious hit.

[@anvarisamukhamedov4961]

Love it, thanks for the video. Where can I buy full series of your videos? I am SAS beginner I would like to have them for personal use. Thanks.

[@SASUsers]

Anvar, thank you so much for the feedback! We offer some free e-learning courses as well as SAS tutorials! Register for our SAS Starter Kit to find out more about these resources and more as you #LearnSAS 2.sas.com/6052HsfDO

[@anvarisamukhamedov4961]

@@SASUsers thank you, will register surely

[@SASUsers]

Perfect! Sounds great!

[@jensbpunkt1103]

What if I need to merge a dataset with another dataset instead of with Anna? How do I do that?

[@SASUsers]

Jens, thanks for your inquiry! To merge two data sets using the MERGE statement in the DATA step, the syntax would be as follows: libname mydata 'location-of-SAS-datasets'; data student_info; merge mydata.student_contact mydata.student_grades; by student_ID; run; Both data sets must be in sorted order by student_ID. For more information on the MERGE statement, check out the documentation here. Please send any questions to [e-mail].

[@sarlathakur380]

Plz continue

[@childsworld4430]

thanks a lot for help:)

[@SASUsers]

You're welcome!

[@jitendarreddy98]

Hi Anna, it was a great explanation, I have learned it with ease. I have a question here - Your video taught join using one common variable, what if i have more than 2 common variables to use to complete join using PROC SQL? If you could give with an example would of great help. Thank you in advance.

[@SASUsers]

Thanks for your question! Checking on this for you!

[@SASUsers]

Unfortunately, we cannot post code or screen shots to this feedback area. We cover joins in detail in our SQL 1 class: 2.sas.com/6052HNPak . Your best resources for a question like this are either to open a track with Tech Support or to post your question in the SAS Community Forum. The SAS Community Forum area is monitored by experienced SAS users and SAS employees. The SAS Programming Community will probably be of the most interest: 2.sas.com/6053HNPaZ . Here are some useful examples: 2.sas.com/6054HNPaw and 2.sas.com/6055HNPab and 2.sas.com/6056HNPaj .

[@BeerMonies]

Vunderful video. Check out some of Bill Jellen's videos on Excel. I love the format that he uses for teaching he jumps right into it...Nothing extraneous.. What he's trying to tackle, and then he does it.

[@custer2449]

like at a university. so is it a university or like a university?

[@SASUsers]

Hi Custer, the datasets shown could relate to a university or various levels of schooling. Please let us know if that is what you were asking about or if you had a different question. We are happy to help!

[@harimanda6506]

Hi Anna, I have a question. Is it possible to zip .xlsx/.xls (with multiple sheets inside them) files through sas??

[@SASUsers]

Hello, Hari, and thank you for your question! Please see a post on this topic in our SAS Communities: Zipping xlsx files 2.sas.com/6056HdT1u We hope that helps!

[@mayanksingh9088]

I am looking for update and joins in proc SQL i.e updating the columns of one table by joining it with other table in proc SQL...can you help?

[@SASUsers]

Great question, Mayank. We are looking into resources for you.

[@SASUsers]

Here are a few resources that may help: 2.sas.com/6052HJTis 2.sas.com/6053HJTit Please let us know if these are not what you are looking for and we can look into this further for you.

[@AllahIsInvincibleYesHeIs]

is SAS important to Data Analytics and Big Data anlytics too

[@SASUsers]

Absolutely! For more information on what it is and why it matters, take a peek here! Big Data Analytics | What it is and why it matters www.sas.com/en_us/insights/analytics/big-data-analytics.html Big Data Insights www.sas.com/en_us/insights/big-data.html Advanced Analytics www.sas.com/en_us/solutions/analytics.html Let us know if this helps provide more insight.

[@GuilhermeGoncalves-ss8mf]

Bom dia. Tudo bem? Fiz um programa utilizando inner join e funcionou perfeitamente, porém quando eu coloco a importação para ser através de um prompt a leitura dos arquivos é feita corretamente, mas quando solicito para imprimir uma das tabelas totalmente com o comando *, aparece um erro. Saberia me dizer se preciso utilizar outro comando nesse caso?

[@SASUsers]

Guilherme, obrigado pela pergunta! Estamos verificando isso para você!

[@SASUsers]

Não podemos examinar seu código ou dados ou postar qualquer código ou capturas de tela aqui na área de feedback do KZbin. Sugerimos que você abra um chamado com nosso time de Suporte Técnico SAS ou poste sua pergunta e uma amostra de seus dados na Comunidade SAS Brasil. Para abrir uma chamado com o Suporte Técnico, preencha o formulário neste link: 2.sas.com/6058yoY4i Para postar uma pergunta sobre programação na nossa comunidade SAS Brasil, clique no link abaixo e poste no fórum de programação SAS: 2.sas.com/6059yoY4c

[@Explorer_00-p5n]

awesome thanks a lot. Helps a lot. Can you also do " running sas on Unix"

[@SASUsers]

Thank you for sharing! We’ve passed your feedback on to our Media team. In the meantime, here is a link you may find handy: support.sas.com/en/documentation/install-center/94/guide-for-unix.html as well as the Playlist: kzbin.info/door/WOfmTlbeesYiDJNflqsWQAplaylists

[@naveensandilya23]

Well explained in a simple way!! Thank you !! could you please assist me with the below query? what is the use of outer union corr here in this query? proc sql; select * from sasuser.admit outer union corr/*what is this significant?*/ select sum(fee) as fee from sasuser.admit; quit; please guide?

[@SASUsers]

Ratnakar, thank you for your inquiry! We will look into this for you and will follow up!

[@SASUsers]

Ratnakar, please see more information below on your request. IN SQL the OUTTER UNION is going to combine ALL the columns from Table A and ALL the columns from Table B, by default it will not overlay and same named columns. Simply but all columns from table A then all columns from table B. When you use the COOR modifier CORRESPONDING (CORR) overlays columns that have the same name in both tables. When used with EXCEPT, INTERSECT, and UNION, CORR suppresses columns that are not in both tables. When used with OUTTER UNION only overlays same named columns. Here is a link to the SAS Help 2.sas.com/6058E2zmg

[@naveensandilya23]

SAS Users Thank you so much!!

[@gandytim7359]

How do I put numbers 94303 into numeric

[@SASUsers]

Gandy, thank you for your inquiry! We are checking on this for you!

[@SASUsers]

Gandy, it's not clear where in the SQL join example in this video you have a question since the instructor uses datasets that are already created. If you take or have taken our Programming 1 class, we cover creating new variables or columns in that video. You can create numeric variables with constant values such as 94303 either in a DATA Step program with an assignment statement or in a PROC SQL program using either SELECT or INSERT; however, the syntax would be different depending on the technique you used. We cannot post code or screen shots in this KZbin feedback area, so you may have to look for other resources for your question. If you have questions about working with your own data and creating new variables, you might want to take a look at our Programming 1 class, which covers this topic. Here's how you can activate the free Programming 1 e-learning class -- just click the Start button on this page: 2.sas.com/6052KEs7w . Or, you can post a question in the SAS Community Forum for SAS Programming at this link: 2.sas.com/6053KEs7b. If you are working with your company license for SAS and working with data for your job, then you can also open a track with SAS Tech Support by filling out the form at this link: 2.sas.com/6054KEs7j and giving us your site license number and other information.

[@gulammohiddin5747]

Great tutorial. And you look lovely

[@SASUsers]

Great! Thanks for sharing, we’re glad you enjoyed the content!

[@Th3N3xtDoctor]

I love you

[@SASUsers]

We love you right back!