SAS Samples

[Fuzemmo]

This is a very general question. I have a pretty extensive Microsoft SQL background, particularly with writing/designing complex queries. I need to make the leap to SAS and start converting some of my tasks. I'm looking for some good resources, hopefully with examples, to replicate some basic tasks that I performed in SQL, such as joining data from multiple datasets, updating, aggregating, etc...

I've used the PROC SQL; commands to do these tasks, but it is painfully slow. Using PROC SQL, it took over 30 minutes to delete 250,000 rows of data from a particular data set. Using DATA, SET and DELETE I did the same delete in about 30 seconds. What I'm looking for are the non- "proc sql" equivelants.

I'm a pretty fast learner and prefer online resources, but am not against buying printed materials if that can be recommended as well.

Thank-you in advance.
Jim

 

[klaz2002]

Perhaps you can post a typical query in pseudo code and I could try to give you the sas datastep equiv. I also think that you did not write the proc sql query correctly. The reason I suggest this is that in my experience I get the Proc SQL proc to perform much faster than a normal data step.
Klaz

 

[Fuzemmo]

Absolutely Klaz, and thank-you for your reply. First, let me give some sample dataset defines.

DS1
(col1 string 40)

DS2
(col1 string 1) (col2 string 9) (col3 string 5)
(col4 number)

DS3
(col1 number) (col2 string 5) (col3 number)

DS4
(col1 string 5) (col2 string 9) (col3 number)

Now, given those datasets, here are some examples of things that I'd like to be able to do, starting with some basics.

1. Delete from DS2 where col1 = 'A'

2. Move data from DS1 into DS2 where...
DS2.col1 = substring(DS1.col1, start = 1, len = 1)
DS2.col2 = substring(DS1.col1, start = 10, len = 9)
DS2.col3 = substring(DS1.col1, start = 20, len = 5)
DS2.col4 = cast to a number (substring(DS1.col1, 30,2))

3. Group the data in DS2 by col3, summing col4, and store the results in DS3 columns 2 and 3. Assume a constant is inserted into DS3.col1. I only want records from DS2 where col1 = 'A' or 'B' or '6' or '9' and also only want records where the "summed" value to go into DS3.col3 is greater than 100.

4. Join DS2 with DS3 on DS2.col3 = DS3.col2, storing the results in DS4 where DS4.col1 will contain DS2.col3, DS4.col2 will contain DS2.col2 and DS4.col3 will contain DS3.col3

Again, thank-you very much for any assistance that you can provide.

Jim

 

[dje]

I have had better luck bringing the data of interest into SAS with proc SQL ( trimming the volume down if possible). and then do the manipulating necessary with SAS coding. Depending on the file sizes that you are dealing with it may not be practical. Just something to consider.

dje