I need to export a very large dataset to csv, but I need to break the output into multiple, smaller files for the downstream process.  The split need to incorporate logic so that certain groups of records are not split across files.  Can anyone suggest ways to do this? My current plan: look at what proc export sticks in the log (it seems to be a macro, despite the name), scrape that code and doctor it do the split logic.  I'm pretty sure this will work (I haven't seen the spec for the logic yet), but there may be a less hacky way.  If so, RSVP.export ... View more

Preface: I've been working with SAS for 20 years but slightly longer with sql and databases.  So when I need to join data, I use proc sql.  But SAS is a multi-headed beast (or maybe a multi-function tool like a Leatherman) and I think there is a data step/merge technique that can help me.  I'm reaching out before jumping right into the code because I'm afraid of being bitten in the backside by one of SAS's many heads.  (It's happened before so once bitten, twice shy.) Here's the problem:  My data comes from a third party and is not well conditioned. I have a dataset of orders that have an ID (a unique key) and some other fields that can identify a row (part number, order time, and quantity) but not uniquely.  I also have a dataset of order cancellations, but they do not refer back to the order ID being cancelled, instead the cancellation contains some of the non-unique fields (part number and quantity).  It is true, however, that an order can only be cancelled once and that a cancellation refers to only one order (i.e. cancellations get used up once they cancel their order). You can see how sql (left) joining cancellations to orders on part number and qty won't work: the non-uniqueness will cause a multiplicity in the output.  But you can also see how one could match a cancel to its order, say, by matching it with the oldest order that has the same part number and quantity and marking both the order and cancellation as "matched" so they will not match again.  This requires a kind of loop that sql does not do, but that a crafty data step probably does well.  But I've avoided crafty data steps (mostly because I used to work with data designed to work in sql databases) until now and lack the craft. Would anyone like to enlighten me? ... View more

I'm trying to read a file with 6.7M records, but my data step quits after only 105k with no errors.  There are control codes in the file that convinces SAS that the file has ended.  The file is an exported .csv file from the RapidSQL query tool on the same platform (WinXP, SAS 9.2).  (FYI, when I tried to read the data directly from the server via ODBC, it got stuck.  It ran overnight; I killed it and got no results.)  I can import the first million rows into excel, and, indeed it contains a funny character in the line that SAS stopped on.  It's character hex 1A, which is ctrl-z, the EOF character. Q: Can control characters be cleaned out using SAS? The (vastly simplified) code:     data blah ;         infile "textfile.csv" lrecl=32767 ;         input; An excerpt from the log: NOTE: The infile "textfile.csv" is:       Filename=C:\Projects\xxx\textfile.csv,       RECFM=V,LRECL=32767,       File Size (bytes)=962846799,       Last Modified=16Jan2013:13:19:58,       Create Time=16Jan2013:13:04:55 NOTE: 105454 records were read from the infile "textfile.csv".       The minimum record length was 128.       The maximum record length was 146. ... View more

I have data in normal form: key1, key2, value.  I'd like to compute the correlation of key2 pairs across key1s.  For example if key1 are dates and key2 are people and the values are their test scores, I want to correlate pairwise test scores of people over time.  I also want to capture the stat output (p-values, nobs) for each correlation coefficient.  The end result should be in normal form: key21, key22, corr, p-value, nobs.  For example, each row would contain the correlation of test scores of 2 people (identified by key21 and key22) etc. Since proc corr wants the entities to be correlated in columns, I need to pivot the input data, compute the corr, capture the output, and un-pivot the output.  Given that data is often stored in normal form, I was hoping there was an easy way to do all this.  I can't find it.  Here's what I came up with.  Let me know of a better way. * pivot normalized data into a table; proc transpose data=Activity out=ActivityPivot prefix=H;      by Key1;      var Value2Correlate;      id Key2; * Save output, including p-values; * Output contains a var named Variable which holds Key2 values; proc corr data=ActivityPivot ;      ods output PearsonCorr = IndividualCorr; * un-pivot correlation output to normal form; proc sort data=IndividualCorr;      by Variable; proc transpose data=IndividualCorr out=blah;      by Variable; * construct a nice output format: Key21 Key22 (identify the pairwise correlation)  Corr, p-value, nobs; data CorrCorr;      set blah( rename=(Col1=Value) where=(_NAME_ like 'H%'));      Key21 = substr( Variable, 2);      Key22 = substr( _NAME_, 2);      drop Variable _NAME_;      run; data CorrN;      set blah( rename=(Col1=Value) where=(_NAME_ like 'NH%'));      Key21 = substr( Variable, 2);      Key22 = substr( _NAME_, 3);      drop Variable _NAME_;      run; data CorrP;      set blah( rename=(Col1=Value) where=(_NAME_ like 'PH%'));      Key21 = substr( Variable, 2);      Key22 = substr( _NAME_, 3);      drop Variable _NAME_;      run; proc sql;      create table Here.HolderCorr as      select a.Key21, a.Key22, a.Value as Correlation, b.Value as PValue, c.Value as N      from CorrCorr a      join CorrP b        on a.Key21 = b.Key21       and a.Key22 = b.Key22      join CorrN c        on a.Key21 = c.Key21       and a.Key22 = c.Key22      order by 1,2; PS: why can't I paste code from a text editor?  Ctrl-v didn't work.  I had to paste it into the html editor, causing me to lose all line breaks. ... View more