BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Robin_R
Calcite | Level 5

Hi i am fresher to sas projects.but i have done crs.so i want to learn new things real time oriented.so pls help me.

for example if i import 5 milion records to import on sas.what can i do have get back up and what is the diffrence between proc sql and merge.which is best for real time secnario.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

When SAS creates a table (dataset), it writes to a file named tablename.sas7bdat.lck, which is renamed to tablename.sas7bdat when the step is finished (the original tablename.sas7bdat is deleted, if it existed). If the step fails, the replacement is not done, so the previous data is preserved.

Keep in mind that SAS in itself is not a database management system, so don't expect it to have the same support for transactions and rollbacks like a full-fledged DBMS does. (Advanced SQL features were introduced in 9.4 with the FEDSQL procedure)

 

When working with large data, a combination of SORT and DATA steps may prove to be more efficient than doing the same join in SQL.

OTOH, SQL can do some things that are not possible (or very difficult to achieve) with a DATA step (cartesian products, eg).

It comes down to what exactly needs to be done, and simply testing which is better.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

"what can i do have get back up"

 

????

 

Regarding the difference between SQL and combinations of PROC SORT and DATA steps with MERGE, there are conditions where the latter can be much more performant.

 

Please try to be more comprehensible. A large part of the contributors here are not native English speakers and will have a hard time deciphering your posts. If necessary, write your posts in a word processor first and use the spellchecker.

Robin_R
Calcite | Level 5

Sorry but thanks for your advice.next time let me do.

 

My question is when it is coming joining the tables which is best PROC SQL or Merge Statement?

 

And How can I take back up from SAS when it is interrupted in the middle of the process?

Kurt_Bremser
Super User

When SAS creates a table (dataset), it writes to a file named tablename.sas7bdat.lck, which is renamed to tablename.sas7bdat when the step is finished (the original tablename.sas7bdat is deleted, if it existed). If the step fails, the replacement is not done, so the previous data is preserved.

Keep in mind that SAS in itself is not a database management system, so don't expect it to have the same support for transactions and rollbacks like a full-fledged DBMS does. (Advanced SQL features were introduced in 9.4 with the FEDSQL procedure)

 

When working with large data, a combination of SORT and DATA steps may prove to be more efficient than doing the same join in SQL.

OTOH, SQL can do some things that are not possible (or very difficult to achieve) with a DATA step (cartesian products, eg).

It comes down to what exactly needs to be done, and simply testing which is better.

LinusH
Tourmaline | Level 20

For over 20 years, this has been of the most frequent question within the SAS community.

A simple search would give tons of papers and posts. Please study them, and please return with a more specific matter.

Good luck!

Data never sleeps
LinusH
Tourmaline | Level 20

Well, we could give bit and pieces, but when I don't see the full picture, the suggestion may not be appropriate.

First of all, loading 5'' records (in one instance) doesn't sound like real time to me, rather batch oriented. Perhaps near real time - whet is the applications?

If you want to do real real time with SAS, I thin the Event Stream Processing the the appropriate product to use. It desn't use Base SAS (at least not in the stream part).

 

Also, please define "in the middle of the process". What is the process? A whole ETL job, or a single SQL statement? And what is the requirement?

Data never sleeps
Reeza
Super User

SAS isn't really designed for real time reporting.  

 

Re merges it also depends on where your data is stored. For example if it's stored on a server SAS can pass tasks to the server which are usually faster than your desktop. 

 

So Merge in certain cases and joins in other cases. And if it's a many to many merge that requires a cross join you have to use SQL. 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 943 views
  • 1 like
  • 4 in conversation