cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
jdmarino
‎09-01-2015
  • 78 Posts
  • 26 Likes Given
  • 0 Solutions
  • 5 Likes Received

Re: Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-13-2014 10:13 AM
1 Like
‎02-13-2014 10:13 AM
1 Like
This is the right way to do it.  Took 18 seconds on 500k rows.  I don't know why this did not occur to me; I use proc rank pretty often.  Viva la Community! ... View more

Re: Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-13-2014 09:46 AM
‎02-13-2014 09:46 AM
http://support.sas.com/cdlsearch?charset=iso-8859-1&nh=25&ct=80000&qt=nextobs++spr%3A%229.3qq_%22&sasreleasepart=9.3qq_&s1=3&searchterm=nextobs&sasprodpart=&searchContains= I searched for "nextobs" and found nothing.  But that's ok: Jag's solution seems right. ... View more

Re: Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-13-2014 08:59 AM
‎02-13-2014 08:59 AM
I can't find those options mentioned for proc univariate.  I have SAS 9.3; could those be new options? ... View more

Re: Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-13-2014 07:55 AM
‎02-13-2014 07:55 AM
You know, I have used this technique before, and it does work on my sample data.  I'll try it on my big dataset, but the intermediate data may become too large for my machine. Update: after an hour, it's still running!  No wonder I put that technique out of my brain.  Message was edited by: John Marino ... View more

Re: Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-13-2014 07:34 AM
‎02-13-2014 07:34 AM
The rule for ties: do what is easiest/most obvious.  If that means including all rows that are tied so that the total count per group > N, that's OK. The data can be sorted or indexed. FYI, I'm pretty sure I can do this in a data-step, but that seems a little "grow you own".  I'm guessing there is a proc or feature I've never heard of that will do it for me.  Hence this post. ... View more

Top N rows per by-group

by Fluorite | Level 6 in SAS Programming
‎02-12-2014 05:10 PM
‎02-12-2014 05:10 PM
I don't know how to pull out the top N rows per by-group.  I'm a long-time SQL querier, and this kind of thing, where N is specified, is not something SQL is good at.  For example, I'd like to pull out the rows of the top 2 returns per sector.  This needs to work on a large dataset (10M+ rows) where I need the top 100 values in a handful of categories.  All good ideas are welcome. data Stocks;     input Ticker :$3. Sector :$4. Return;     datalines; ABC Tech 0.10 DEF Tech 0.30 GHI Tech 0.01 JKL Tech 0.12 YZA Food 0.01 VWX Food 0.09 STU Food 0.08 PQR Food 0.05 MNO Food 0.02 BCD Booz 0.10 EFG Booz 0.11 GMC Cars 0.01 ; /* desired output: a dataset with the rows which are the top 2 returns in each sector even if there are fewer than N rows in the sector DEF Tech 0.30 JKL Tech 0.12 VWX Food 0.09 STU Food 0.08 BCD Booz 0.10 EFG Booz 0.11 GMC Cars 0.01 */ ... View more

Re: how to calculate holding period of stocks?

by Fluorite | Level 6 in SAS Programming
‎02-11-2014 09:21 AM
‎02-11-2014 09:21 AM
You can sell stock you don't own, it's called "short selling" and it's done all the time.  You have to locate and borrow the shares first (your broker does this).  The goal is always "buy low and sell high", but not necessarily in that order. ... View more

Re: time HHMMSS format

by Fluorite | Level 6 in SAS Programming
‎12-20-2013 09:42 AM
1 Like
‎12-20-2013 09:42 AM
1 Like
This is the way I've done it, but I was hoping there was a more direct way.  Frankly I'm surprised there isn't a built-in format for this. ... View more

Re: Split SAS Output into multiple Sheets

by Fluorite | Level 6 in SAS Procedures
‎10-31-2013 04:37 PM
‎10-31-2013 04:37 PM
When I try this, I get my file with only the last-written sheet.  (In this case Test2.)  To be fair, I'm using dbms=xlsx because I have 64-bit SAS and 32-bit Excel 2010. ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-30-2013 01:53 PM
‎10-30-2013 01:53 PM
1. No: I used an index 2. No.  Plenty of drive space ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-30-2013 01:52 PM
‎10-30-2013 01:52 PM
Yep. I've got the basics working (had problems when the BY group was larger than the row threshold for a file).  I'm about to research dataset introspection so I can build a list of the fields to output.  _all_ is no good for me because of the extra fields I added. ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-28-2013 05:24 PM
‎10-28-2013 05:24 PM
Well, I was disappointed today.  I tested my code on a 100K row dataset, splitting into 10k chunks but not splitting across a special set of vars.  It worked.  BUT, when I ran it on the real 47M row dataset, I got ERROR: The SAS System stopped processing this step because of insufficient memory. on a machine with 24Gig of RAM and a Terabyte of drive space. Now, to try Tom's approach. ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-28-2013 01:13 PM
‎10-28-2013 01:13 PM
Thanks. I'd like to write a general purpose macro (although that's a secondary priority),so I'd certainly prefer to only turn off the dots for the proc print and not in general.  Maybe I can detect the state of that option and restore it after I do my work. edit:     %let missopt = %sysfunc(getoption(missing,keyword)); /* store the existing option away */     options missing=''; ...     options &missopt; This seems to be what I wanted. Interesting about the google search; I've been searching both my local help file and support.sas.com and not getting good results.  (I get results, just not relevant one.) ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-28-2013 12:47 PM
‎10-28-2013 12:47 PM
Cynthia, I'm trying your solution because it seems require the least coding on my part.  I have to say, however, that ODS is a multi-headed monster, and I can't find anything in the help about "ods csv".  (I can find "ods csvall", but that's a different beast.)  Can you point me to some documentation? Second question: I'd like NULLs in my dataset to be represented by consecutive delimiters (i.e. an empty field), but I'm getting a '.' (missing).  Can you tell me how to turn off the dot? ... View more

Re: export large dataset to multiple csv files

by Fluorite | Level 6 in SAS Programming
‎10-21-2013 08:43 AM
‎10-21-2013 08:43 AM
Thanks, Urvish; it's generous of you to share.  I don't yet know the split logic (I'm helping a friend and he hasn't yet provided me the spec).  But more importantly, I'm not exporting to excel files, but to actual .CSVs, thus the multisheet approach does not apply.  (A .csv is a pure text file; there are no sheets.) ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-01-2015 07:11 AM
Likes given to
Likes from
Quick links