I would change year = 9999 to missing then use MISSING DESCENDING on class statement. [pre] data test; input id:$2. date:monyy.; if year(date) eq 9999 then date = .; format date monyy.; cards; 01 sep9999 01 nov2008 02 sep2008 02 sep2007 ;;;; run; proc print; run; proc format; value nadate . = 'N/A' other = [monyy7.]; run; proc tabulate; class date / missing descending; tables date,all; format date nadate.; run; [/pre] ... View more

I have EXECL 2003 and get the proper result with when using the updated TAGSET. With this really old verion NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05). TAGATTR seems to be ignored. Upgrading to NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). I get the correct result. ... View more

I can tell you the problem but I don't know what happened to your program probably a macro variable with null value. In the INFILE statement the first parameter is positional and is FILEREF or Physical file. In your code that is missing and TRUNCOVER infile is now the first parameter. I think SAS then says "truncover is too long for a fileref then it must be a physical file but I can't find" error message. > ERROR: Physical file does not exist, > /usr/local/sas/***/Lev1/SASNew/TRUNCOVER. > INFILE > TRUNCOVER > DSD > %IF &IsMVS=FALSE %THEN %DO; > LRECL=32767 ... View more

This may not be what you want but I think it produces the output you desire. Seems to me the only problem is the TOTAL which I don't believe PROC REPORT will produce using BREAK statements, but if you create a total ID group you can get the desired output. [pre] proc plan noprint seed=469844687; factors id=3 ordered year=3 ordered y1 = 1 of 100 y2 = 1 of 200 ; output out=plan id cvals=('A' 'B' 'C') year nvals=(2006 2007 2008); run; proc print; run; data planV / view=planV; set plan; output; ID = 'T'; output; run; proc report nowd list headline; columns id year y1 y2 y3; define id / group; define year / group; compute y3; y3 = sum(y1.sum,y2.sum); endcomp; run; [/pre] ... View more

You can avoid the use of QUOTING functions in this case with the use of parenthesis(). Notice the change in your code where &SB is used in the macro, I removed the parenthesis because they are now part of the value. If you had a situation where the parenthiesis were not needed they could be removed with %qsubstr(%superq(sb),2,%length(%superq(sb))-2); [pre] 433 %let sc = '1224','1612','9603','9919','1920'; 434 %let FACTOR = ?; **Where is this?; 435 436 %macro sb_metrics(sb=('9999'), sb_txt_shrt=none, 436! sb_txt_lng=none); 437 %put _LOCAL_; 438 %put , count(distinct case when C.itm_sbrnd_cd in &sb 438! then A.lbi_indv_id else . end)&factor as 438! &sb_txt_shrt._indv_cnt 439 /* more code here but same concept */. 440 &factor as &sb_txt_shrt._indv_cnt 441 ; 442 %mend sb_metrics; 443 444 %sb_metrics(sb=(&sc)) SB_METRICS SB_TXT_SHRT none SB_METRICS SB_TXT_LNG none SB_METRICS SB ('1224','1612','9603','9919','1920') , count(distinct case when C.itm_sbrnd_cd in ('1224','1612','9603','9919','1920') then A.lbi_indv_id else . end)? as none_indv_cnt . ? as none_indv_cnt [pre] ... View more

Disclaimer: I'm not an expert. This problem is resolved with %UNQUOTE. Sometimes automatic unquoting does not work and UNQUOTEing is needed. Notice that the data step produces two data sets WORK.TEST and WORK.A. No error but not the intended result. Then in PROC PRINT data = TESTA causes another error. There are no spaces rather two tokens. ... View more

Use redirection to send STDERR to STDOUT. cmd args 2>&1 The syntax may be wrong you will need to check. Then in your data step read any lines returned when the command is executed and proceed as needed. By default any STDERR output from commands executed with PIPE should be showing up in the SASLOG. ... View more

This is a common problem solved with %UNQUOTE. systask command %unquote(%str(%')&BatchIt%str(%')); ... View more

I "think" you need FORMULA.FIND. I was not able to test. put '[formula.find(200847,2,1,1)]'; Syntax FORMULA.FIND(text, in_num, at_num, by_num, dir_num, match_case) FORMULA.FIND?(text, in_num, at_num, by_num, dir_num, match_case) Text is the text you want to find. Text corresponds to the Find What box in the Find dialog box. In_num is a number from 1 to 3 specifying where to search. In_num Searches 1 Formulas 2 Values 3 Notes At_num is the number 1 or 2 and specifies whether to find cells containing only text or also cells containing text within a longer string of characters. At_num Searches for text as 1 A whole string (the only value in the cell) 2 Either a whole string or part of a longer string By_num is the number 1 or 2 and specifies whether to search by rows or by columns. By_num Searches by 1 Rows 2 Columns Dir_num is the number 1 or 2 and specifies whether to search for the next or previous occurrence of text. Dir_num Searches for 1 or omitted The next occurrence of text 2 The previous occurrence of text Match_case is a logical value corresponding to the Match Case check box in the Find dialog box. If match_case is TRUE, Microsoft Excel matches characters exactly, including uppercase and lowercase; if FALSE or omitted, matching is not case-sensitive. ... View more

What version of SAS are you using? With 9.1.3 the PROC FCMP works but the function cannot be call from a data step. I think you need 9.2 for that. ... View more

I don't know how to get the OPEN behavior to change. From windows Explorer, to open a file in EE I just drag and drop it into/onto an EE window. The file is opened in EE in a new tab. You can select a number of files and drop them -- all opened in separate EE tabs. While not what you want perhaps an acceptable alternative. ... View more

I think this is what you want but my results do not match your expectation exactly. You do this by looking ahead by usually "I think" it is done by looking back. [pre] dm 'clear log; clear output;'; data cars; input Obs VehicleNumber :$8. EntryDateTime & datetime. GateID:$1.; format E: dateampm19.; drop obs; cards; 1 SGG9274A 12-Jan-07 02:18:27 AM A 2 SSC7789N 16-May-07 04:45:58 PM B 3 SSC7989N 16-May-07 11:00:29 PM B 4 SSC7989N 05-Jul-07 06:33:38 PM B 5 SSC7989N 18-Jul-07 12:22:53 PM C 6 STM1209B 28-Feb-07 05:07:31 AM A 7 SVX3120C 06-Sep-07 09:29:20 AM A 8 SVX3120C 21-Sep-07 07:23:48 AM C 9 SVX3120C 21-Sep-07 07:55:28 AM C 10 SVY3542N 09-Mar-07 03:06:24 PM B 11 SVY3542N 19-Mar-07 12:21:02 AM B ;;;; run; proc sort; by V: E:; run; data cars; do until(last.VehicleNumber); set cars; by v: E:; FromGate = lag(GateID); RunTime = dif(EntryDateTime); ToGate = GateID; if first.VehicleNumber then call missing(of FromGate RunTime); if not first.VehicleNumber then output; end; format runTime comma12.; drop Entry: GateID; drop run; proc print; by v:; id v:; run; Vehicle From To Number Gate RunTime Gate SSC7989N B 4,303,989 B B 1,100,955 C SVX3120C A 1,288,468 C C 1,900 C SVY3542N B 810,878 B [pre] ... View more

I have had some success with this using SET with the POINT= option. This example is completely contrived but it may serve well enough to illustrate a way to initialize a list of variables without looping over all the elements of an array. [pre] data initArray; array v[5]; retain v 0; run; data workWithArray; array v[5]; do i = 1 to 6; if i in(1,2,4) then set initArray point=point; else do j = 1 to dim(v); v = ranuni(1); end; output; end; retain point 1; stop; run; proc print; run; [pre] ... View more

Are you trying to produce ISO 8601 date time intervals similar to the following... P2Y3M4DT5H6M7S You did not mention the version of SAS you are using but there are new 9.2 FUNCTIONS, FORMATS and INFORMATS related to this. Start by looking at CALL IS8601_CONVERT Routine. I don't have access to 9.2 so I have not bee able to try these function. ... View more

See if this looks right to you [pre] data test; input id:$4. date:ddmmyy. action; format date date.; cards; 1234 07/01/2008 0 1234 01/06/2008 0 1234 11/07/2008 0 1234 16/07/2008 1 1234 08/08/2008 0 1234 02/10/2008 0 4567 27/02/2008 0 4567 22/06/2008 0 4567 20/07/2008 0 4567 12/09/2008 1 7891 14/02/2008 0 7891 08/04/2008 0 7891 20/04/2008 0 7891 04/07/2008 1 7891 14/07/2008 0 7891 27/09/2008 0 7891 16/10/2008 0 ;;;; run; data actioned; do until(last.id); set test; by id; if not actioned then output; actioned = actioned max action; end; drop actioned; run; proc print; run; [pre] ... View more