In my experience, this kind of problem usually requires stripping out carriage returns using a preprocessing step. I use the following macro: %macro replace_crlf(inf,repchar='.'); data _null_; infile "&inf." recfm=n sharebuffers; file "&inf." recfm=n; input a $char1.; retain open 0; ** This statement toggles the open flag. ; if a='"' then open=not open; if a in ('0A'x,'0D'x) and open then put &repchar.; run; %mend replace_crlf; %replace_crlf(filename.csv,repchar=' '); This will alter the file in place...so if you don't want to risk losing the original, take a copy of it first! Good luck. ... View more

Hello. Try this and check the values of rc: data rc; rc=system("xcopy \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1"); output; rc=system("move \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1"); output; rc=system("copy \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1"); output; run; Once you get something that works you can try it again in %sysExec, or keep the above with data _NULL_ instead of data rc (although it can be quite useful to have a work dataset where you can check the return codes). If you have a drive mapping for \\133.9.206.139\bdp then try using that instead. Tim ... View more

I think you were almost there. Try this... Data comb_rate;     Set female_pr male_pr pulserate;     label rate_change='Pulse Rate Change'         BMI='Body Mass Index';     if gender='male' then     do;       BMI=703*weight/(height*height);     end;     if gender='female' then     do;     BMI=695*weight/(height*height);     end;     Rate_change=rate_after-rate_before; run; proc print data=comb_rate; run; proc format;     value bmifmt         Low-25='Normal'         26-35 ='Low Risk'         36-High='High Risk'; run; proc print data=comb_rate label;     FORMAT BMI bmifmt.; run; ... View more

Hello ajuhack, I have attached 3 macros that you might like to try. The main macro is called %read_csv_data.sas  -  it calls the other 2 macros. I have learned by experience not to trust proc import for the very reason that it uses guessingrows. If you are guessing, you will always run the risk of data truncation. My solution is to perform 2 passes (in the way Cynthia suggested). Actually, because of my environment, it also reads the top 100 lines first, before doing the full reads, to determine if lines are CRLF or LF terminated! It searches the top 100 lines for a header record, in order to work out column names. It searches for a delimiter, or you can specify it if you know it. It reads all of the data into character variables - so you will need to convert numerics and dates afterwards. In my test file of 95,000 obs it takes about 1 minute to run on my AIX server. My other test file of 410MB and over 10 million obs takes 8 or 9 minutes. SAS is good like this - the first full read of the text file takes 7.5 minutes, and the second read takes 40 seconds. I hope you find this useful. Tim. ... View more

I also agree with SASKiwi; I cannot use proc import with Excel files on my AIX server. If you are using a data step, you could try using the termstr=CRLF option on the infile statement - if your file was saved on a Windows platform. SandorSzalma is correct that you can still use proc import on unix, as it can still be used to read text files (delimited). HOWEVER, if you use proc import with a tab delimited file it only looks at the first 2 rows by default to guess variable lengths. You can change this by using the GUESSINGROWS= option. ... View more

To try to answer your original question, have you tried this?... data out;   set ordered (obs=0); ... run; If you include the ordered dataset as the first in the set statement, but with obs=0, it should define the variables you want in the order you want. ... View more

Actually, I suggested another approach...but I don't have time to time it ... View more

This should run as quickly as Tom's SQL, I hope (although it is more verbose!): data lastonly (drop=t_:);   set have (rename=(ColA=t_A ColB=t_B ColC=t_C));   by Period;   length ColA ColB ColC 8.;   retain ColA ColB ColC;   if first.Period then do;     ColA = .;     ColB = .;     ColC = .;   end;   ColA = max(ColA,t_A);   ColB = max(ColB,t_B);   ColC = max(ColC,t_C);   if last.Period then output; run; ... View more

In that case, if you want to reset the syserr value each time, change the %runquit macro to: %macro runquit; %* Reset the syserr value; data _null_; x=x; run; %mend runquit; ... View more