If the enoding problem happened upon the creation of XML, then it's probably necessary to "preprocess" the XML text prior to reading it (like Chris suggested above) Search for things LIKE "&#___;" ... that are bad translations of HTML codes I removed them with this code: line1_fixed = prxchange('s/&#([0-9])+;//',-1, line1); ... View more

@TMiles wrote:   I am having trouble attaching a SAS dataset -how can I put a sample of the data here? Instructions here will show how to turn a data set into datastep code: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712   To post just a few records subset your data such as with the OBS dataset option on a set statement. I also recomment removing variables that are not concerned with the issue at hand (drop or keep as needed). Then either paste the resulting data step code into a code box using the {i} icon or attach as a text file. ... View more

Wow @RogerSpeas, you win the award for updating the oldest thread this week! (This post was originally from 2007.)   To answer your question, the OLE DB provider is not a factor during the Upload or Download.  In the original post, I just wanted to point out that when you don't use the Upload task, you can still read local data set files with EG and EG will transfer those records to SAS as needed -- but it's slow.  The Upload task makes the transfer a faster operation -- using a binary FTP-like transfer.   After Upload, the task checks to see if the data set encoding matches that of the SAS session and if it doesn't, it runs a DATA step to fix it up. ... View more

Thanks for the lesson, @RogerSpeas.  As I mentioned, I don't write a lot of SQL.  Guess I had hoped with the correlated subquery that the optimizer might be smart enough to sort both tables (if not indexed), and then approach the correlated sub-query with something like the double-DOW loop approach that Astounding used (point being to process each by-group in turn, so would only need one pass of each dataset after the sorts).   But that's probably hoping for too much.  Correlated sub-queries make me scratch my head.   And I like your last solution, with joining on the pre-summarized table.  That's the kind of SQL where I don't have to scratch my head, and I think "wow, it's fun to read SQL, because all you have to think about is sets."  I spent so much time learning the intracies of the DATA step processes for merges and BY group interleaving and DOW loops.... it took me a long time to get over the fact that with SQL, I wasn't telling SAS how to do something, I was telling SAS what I wanted done.   Cheers, --Q. ... View more

My bad, I thought the objective was different formats on each row...   Obs Obs small medium large elem middle high all 1 1 I II III I 0002 0003 0004 4 1 one two three one two three four     data row1;    infile datalines dsd truncover;    input small medium large elem middle high all ;    format small--elem roman6. middle--all z4. ;    datalines; 1,2,3,1,2,3,4 ; run; data row2;    infile datalines dsd truncover;    input small medium large elem middle high all ;    format _all_ words. ;    datalines; 1,2,3,1,2,3,4 ; run;   ods csv file='c:\temp\schools.csv';    proc print data=row1;    run;    proc print data=row2;    run; ods csv close;   proc import datafile ='c:\temp\schools.csv' dbms=csv out=stacked replace;    getnames=yes; run;   proc print data=stacked;    where obs = 1 ; run;   ... View more