About Tom

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.

Tom · ‎07-18-2011

Given your BY statement: by uniqueid ptdob sex admitdte; 1) the first instance of the identifier (first.uniqueid, first.ptdob, first.admitdte, first.sex). Taken care of with the code above. Just use FIRST.ADMITDTE. You do not need to look at the other first. variables. 2) if first and last instance of all 4 identifying variables (i.e., only one incident per person). Also taken care of above, could add in some specific code if needed like: if (first.uniqueid and first.ptdob and first.admitdte and first.sex and last.uniqueid and last.ptdob and last.admitdte and last.sex) then do; Only one record per id would be tested by: (first.admitdte and last.admitdte) 3) if first instance of only one of the identifying variables, but not the first instance of the others >>e.g., if first.uniqueid=1 and first.ptdob=0, etc. ??? Scenario 3 is my problem. I've tried all sorts of combinations of first. and last. processing with 1 and 0, and am still not getting the right values for diff and casenum. If you want the first instance of the combination UNIQUEID,PTDOB and SEX then use FIRST.SEX. Again you do not need to look at all of them.

Tom · ‎07-17-2011

General PROC SUMMARY (proc means) is the easiest way to summarize the data. For a binary (1/0) variables the mean is the same as the percent that have a 1. So you could do something like this: proc summary data=have nway ; class community ; var sex smoker ; output out=want mean=male_percent smoker_percent ; run;

Tom · ‎07-15-2011

This is a common problem. For example look at this thread. http://listserv.uga.edu/cgi-bin/wa?A2=ind1012B&L=sas-l&P=R14426&D=0 For your problem you could try: data want ; set have nobs=nobs ; next1 = _n_ + 1; if V1 in ('aa','cc') and _n_ < nobs then set have(keep=V1) point=next1 ; run; You will need to expand this to cover all the variables that you want to "move up".

Tom · ‎07-15-2011

Is the variable already pulled into a SAS dataset using SAS/Access? I have not worked with DB2 and did not find any Date9 format for DB2 using google. So I assume that you already have a SAS variable. Do a PROC CONTENTS on your dataset and see if the variable has been created as numeric or character. If it is numeric then you probably just want to change the format attached to the variable using a FORMAT statement. YYMMDDn8. is the SAS format for YYYYMMDD . Try : FORMAT mydatevar YYMMDDn8. ; If you want to create a new character variable then use the PUT function. newvar = PUT(mydatevar, YYMMDDn8.); If you actually have a character variable that is in SAS date9 format then you can use the INPUT function to convert it to a DATE value. newdatevar = INPUT(mycharvar, DATE9.); format newdatevar YYMMDDn8.; Or you might need to combine the two. newvar = PUT( INPUT( mycharvar, DATE9.) , YYMMDDn8.);

Tom · ‎07-15-2011

Is this just a data step issue? If your "table" is in a text file check and see if it has line breaks between rows. Then you can just read it in cell by cell. data table ; infile 'table.txt' dlm='+' dsd ; length row col 8 cell $200 ; row+1; do col=1 by 1 until(cell=' '); input cell @ ; output ; end; run; If you do not have line breaks then you will need to add extra logic to see when to start a new "row". If you have empty cells then you will need to add extra logic to figure out how many cells are in each "row". Now you could transpose this and use ODS to print to HTML (see replies above) or you can easily generate HTML syntax yourself in a data step. data _null_; file 'table.html' ; if _n_=1 then put '<table>'; if eof then put '</table>'; do until (last.row) ; set table end=eof; by row ; if first.row then put '<tr>'; put '<td>' cell +(-1) '</td>'; end; put '</tr>'; run;

Tom · ‎07-15-2011

Did you try using a COMPUTE block in PROC REPORT to output the "BY" line when the value of VAR2 changes?

Tom · ‎07-15-2011

Can you post any error message? Or are you just not getting the expected output. Have you tried the WITH statement in PROC CORR? You should be able to eliminate the %DO K loop from your macro. And hence also the need pull END2 from a dataset. Prehaps something like this? %macro test(end); %do J=1 %to &end ; proc corr data=corrprep outs=out noprint; var I&J ; with I&J.S: ; run; proc append base=out1 data=out (where=((upcase(_name_)="I&J") rename=(I&j = corr) ) ; run; %end; %mend; %test(&end1)

Online Status	Online
Date Last Visited	3 hours ago