About Tom

Tom · ‎07-25-2011

The two main problems I see with doing what I think you want to do with an INFORMAT are what you are inputting and what you are outputting. You are attempting to define a character informat but the MMDDYY format is a numeric format. It outputs a numeric date not a character string. You are attempting to define the inputs as date constants, but informats need character strings as inputs. That is why it complains about overlapping ranges. '01JAN1990' is less than '01JAN2007' which is less than '31DEC2004' because '19' < '20' and '31' > '01'.

Tom · ‎07-22-2011

Use the INPUTN function which allows the second argument to be a variable name. data tmp; Char_date='20110701'; Date_format='yymmdd8.'; num_date = inputn(char_date,date_format); put (char_date date_format num_date) (=) num_date = date9. ; run; Char_date=20110701 Date_format=yymmdd8. num_date=18809 num_date=01JUL2011

Tom · ‎07-22-2011

You can use the LIST statement to get SAS to dump the input records to the log. Then you can check for special characters like '0A'x (linefeed) that might be making SAS think that they line has ended. If you have an embedded linefeed you can use the TERMSTR=CRLF option on the INFILE statement. Another possibility is that the end of that line is empty. You might try using the TRUNCOVER option. Look at this thread. http://communities.sas.com/message/101557 Another possibility is that the lines contain tab ('09'x) characters and that SAS is expanding the tabs into spaces. This would make the input buffer longer as one tab might be expanded into upto 8 spaces.

Tom · ‎07-21-2011

Just make sure to use the GETNAMES=NO setting on PROC IMPORT. Then use PROC TRANSPOSE in SAS. proc transpose data=have out=want; id f1; var f2 - f5 ; run;

Tom · ‎07-20-2011

Use LAG to get previous value. When it is the first record for the admit date then overwrite that with the admit date. data have; informat admdate testdate mmddyy10.; format admdate testdate mmddyy10. ; input pid $ admdate : testdate : group $ @@; cards; 1 1/1/2011 1/3/2011 A 1 1/1/2011 1/9/2011 B 1 3/4/2011 4/2/2011 B 1 3/4/2011 4/8/2011 B 2 6/1/2010 6/4/2011 C 2 6/5/2010 7/4/2011 C 3 7/1/2011 7/9/2011 D run; proc sort; by pid admdate testdate ; run; data want ; set have ; by pid admdate testdate ; prevdate = lag(testdate) ; format prevdate mmddyy10.; if first.admdate then prevdate=admdate; diff = testdate - prevdate ; put pid admdate prevdate testdate diff group ; run; 1 01/01/2011 01/01/2011 01/03/2011 2 A 1 01/01/2011 01/03/2011 01/09/2011 6 B 1 03/04/2011 03/04/2011 04/02/2011 29 B 1 03/04/2011 04/02/2011 04/08/2011 6 B 2 06/01/2010 06/01/2010 06/04/2011 368 C 2 06/05/2010 06/05/2010 07/04/2011 394 C 3 07/01/2011 07/01/2011 07/09/2011 8 D

Tom · ‎07-20-2011

Use %sysevalf( &sysver > 9.2) to test if you are running a version newer than 9.2. I agree that the NOTE is inappropriate. It does seem strange that even when you ask for keyword format it does not return it, but changing the output now might break old code. Also I would like them to fix the strings returned for values that require quotes when assigning the values. For example if you try this code you will get error messages: options missing=" "; %let sopt=%sysfunc(getoption(missing,keyword)); options &sopt; What I have done is something like: %let sopt=formchar="%qsysfunc(getoption(missing))"; Here is the list of options that I indentified a couple of years ago that require that treatment. missing invaliddata formchar formdlim topmargin leftmargin rightmargin bottommargin xprintnm printerpath

Tom · ‎07-20-2011

It helps to look at some example data. data test; do a=1 to 3; do b=1 to 3; do c=1 to 3; output; end; end; end; run; data _null_; set test; by a b ; put (a first.a last.a b first.b last.b c) (=); run; a=1 FIRST.a=1 LAST.a=0 b=1 FIRST.b=1 LAST.b=0 c=1 a=1 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=0 c=2 a=1 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=1 c=3 a=1 FIRST.a=0 LAST.a=0 b=2 FIRST.b=1 LAST.b=0 c=1 a=1 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=0 c=2 a=1 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=1 c=3 a=1 FIRST.a=0 LAST.a=0 b=3 FIRST.b=1 LAST.b=0 c=1 a=1 FIRST.a=0 LAST.a=0 b=3 FIRST.b=0 LAST.b=0 c=2 a=1 FIRST.a=0 LAST.a=1 b=3 FIRST.b=0 LAST.b=1 c=3 a=2 FIRST.a=1 LAST.a=0 b=1 FIRST.b=1 LAST.b=0 c=1 a=2 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=0 c=2 a=2 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=1 c=3 a=2 FIRST.a=0 LAST.a=0 b=2 FIRST.b=1 LAST.b=0 c=1 a=2 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=0 c=2 a=2 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=1 c=3 a=2 FIRST.a=0 LAST.a=0 b=3 FIRST.b=1 LAST.b=0 c=1 a=2 FIRST.a=0 LAST.a=0 b=3 FIRST.b=0 LAST.b=0 c=2 a=2 FIRST.a=0 LAST.a=1 b=3 FIRST.b=0 LAST.b=1 c=3 a=3 FIRST.a=1 LAST.a=0 b=1 FIRST.b=1 LAST.b=0 c=1 a=3 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=0 c=2 a=3 FIRST.a=0 LAST.a=0 b=1 FIRST.b=0 LAST.b=1 c=3 a=3 FIRST.a=0 LAST.a=0 b=2 FIRST.b=1 LAST.b=0 c=1 a=3 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=0 c=2 a=3 FIRST.a=0 LAST.a=0 b=2 FIRST.b=0 LAST.b=1 c=3 a=3 FIRST.a=0 LAST.a=0 b=3 FIRST.b=1 LAST.b=0 c=1 a=3 FIRST.a=0 LAST.a=0 b=3 FIRST.b=0 LAST.b=0 c=2 a=3 FIRST.a=0 LAST.a=1 b=3 FIRST.b=0 LAST.b=1 c=3

Tom · ‎07-20-2011

Could you structure the reports to write to independent files so that they will not interfere with each other. If you still need the combined files then create a step (pehaps in VB) that combines the files that runs when everything is finished. That step should be fast as it is not doing any real calculations.

Tom · ‎07-20-2011

You want to reset the counter when FIRST.DATE is true. You do not care about the status of the FIRST. variables for earlier variables in the BY statement.

Tom · ‎07-19-2011

As long as you are using SAS verison 9 you should be able to use the COUNTW function. %let n=%sysfunc(countw(&macrolist)); Otherwise you can loop and increment your own counter. You can either test for empty using something like: %if (&var = ) %then .... Or test for zero length %if %length(&var)=0 %then ... They test slightly different things, but for this problem they are equivalent as you are using blanks for your delimiter. Here is one way to count. Remember to subtract one because it stops on an empty word. %let n=1 ; %do %while(%scan(&macrolist),&n) ne ) ; %let n=%eval(&n + 1); %end; %let n=%eval(&n -1 );

Tom · ‎07-19-2011

The %str( ) is useful to prevent the macro processor from ignoring the space. When you use single quotes or double quotes around characters they actually become part of the string. This means that %scan(&myvar,&i,%str( )) means something different than %scan(&myvar,&i,' '). The first only uses blanks as the delimiter when scanning. The second one will use either blanks or single quote characters.

Tom · ‎07-19-2011

The program does use the MIN() aggregate function in the HAVING clause. But it is not required that an aggregate statistic be include in the selected variables.

Tom · ‎07-19-2011

You just need to use a summary function in SQL to determine if all of the variables have counts larger than ten. You can do it with or without another grouping variable in the dataset. This code takes advantage of the fact that SAS evaluates logical operations as 1 (true) or 0 (false). data have ; length group $5 ; input Category $ Count; group = 'SMALL' ; output; group = 'LARGE' ; count=10+count; output; cards; Asian 12 American 3 European 25 Unknown 9 All 71 run; proc sql noprint ; create table want as select group , category , count from have group by group having min( (count > 10) or category = 'Unknown' ) = 1 or category in ('All' 'Unknown') order by group ,category ; quit; Obs group Category Count 1 LARGE All 81 2 LARGE American 13 3 LARGE Asian 22 4 LARGE European 35 5 LARGE Unknown 19 6 SMALL All 71 7 SMALL Unknown 9

Tom · ‎07-18-2011

Looks more like an SQL issue than a macro issue. Try taking the generated command from the MPRINT line and running it standalone. I get the following error message: ERROR: File WORK.AS.DATA does not exist.

Tom · ‎07-18-2011

You seem to be saying that you want to split the list of words in &MACROLIST into separate variables. Do you want just the text string? or the value of the macro variable that has that name? Lets do both and call the resulting macro variables CVAR... and CVAL.... (for variable and value). If we assume that you know the value of N then you can do: %do i=1 to &n ; %let cvar&i = %scan(&macrolist,&i,%str( )); %let cval&i = &&&cvar&i ; %end; If you do not know the value of N then you will need to increment the counter variable (I in the code above) manually using %let i=%eval(&i + 1) and test for and empty result from the %scan() function call to terminate the loop. If you want to be able to reference the new macro variables after the currently running macro finishes you might want to add a %global statement inside the %do loop. NOTE: CALL SYMPUT is for use in regular SAS. You should be able to manipulate you macro variables with just macro logic and statements.

Online Status	Online
Date Last Visited	2 hours ago