With a SAS format, PROC SQL has to pull all records from Oracle to your SAS session, apply the format, and then count the record into one of the 12 "sign" groups. You want to know the freq distribution of the Zodiac signs across this population - counts, essentially. But if your data source is in a database, PROC SQL can push the logic into the database process and perform the operation there, and the result could come back a lot quicker.įor example, suppose you want to run a query on a database (Oracle, let's say) of 1 million records - names and birthdays. SAS Formats are pretty fast, and I'm not sure you would see a big difference in a SAS data table. What are the performance differences between running this case statement vs building a format like Chris described? If I were to run this function on say millions of rows would one run faster than the other? Just curious =] Also, this raises a question that I do not know the answer to (I am a fairly new SAS EG user myself). You may have to change it depending on the format of your input. I did not include the 'd behind my date values because in my input table the date column is defined as a string instead of a date. WHEN t1.date BETWEEN '20Jan2000' AND '18Feb2000' THEN 'Aquarius' WHEN t1.date BETWEEN '01Jan2000' AND '19Jan2000' THEN 'Capricorn' WHEN t1.date BETWEEN '22Dec2000' AND '31Dec2000' THEN 'Capricorn' WHEN t1.date BETWEEN '22Nov2000' AND '21Dec2000' THEN 'Sagittarious' The following is a case statement that *should* produce identical results:ĬASE WHEN t1.date BETWEEN '21Mar2000' AND '19Apr2000' THEN 'Aries' It would work in much the same way as the method Chris described above but does not require the creation of a format. Mdy(month(date),day(date),2000) as sign format=sign.Īnother way to go about this would be to populate a calculated column using a case statement. * that don't span the calendar boundary */ * split Capricorn to make two valid ranges */ * SAS format for zodiac signs in a given year */ format in the column properties.įollowing: complete program that shows it in action. I used Year 2000 because it was a leap year and accounts for Feb 29. In the Query Builder, compute a new column based on the date value, with the month, day, and a fixed year. Zodiac signs don't change from year-to-year (do they?). That's why you need to recode the dates for use with the format - take the Year out of the equation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |