| David Franklin, Blog | |
| 2004 Archive | |
| Back | dfranklinnh@gmail.com |
|
JanuaryThis is not directly a SAS related tip this month but a DOS .BAT file that I find useful when writing programs and wanting to keep a copy of a file with a date/time stamp embedded in the file name, in a backup directory, directly under the directory I am working in. The code for this file is the following: @ECHO OFF
if z%1 == z goto usage
If not exist bkup\Nul MD bkup
FOR /F "TOKENS=2-4 DELIMS=/ " %%F IN ('DATE /T') DO (SET TODAY=%%F-%%G-%%H)
FOR /F "TOKENS=1-2 DELIMS=: " %%I IN ('TIME /T') DO (SET NOW=%%I%%J)
copy %~n1%~x1 bkup\%~n1-%today%-%now%%~x1
goto end
:usage
echo. Usage is BKUP filename.
:end
The usage for this file, where the code is stored in the file BKUP.BAT, is BKUP filename An example of its usage is the file DEMO.SAS where after running the command BKUP DEMO.SAS the file would appear in the BKUP directory with the name DEMO-01-13-2004-1238.SAS where the "01-13-2004" is the date in local format from the DOS country setting and the "1238" is the time. This code will only work when your operating system is Windows NT, Windows 2000 or Windows XP - it will not work in any other environment. Note that if the directory BKUP is not present then it will be created. Hope this is useful to at least a few people. FebruaryThere are a few of ways in SAS that limit the number of observations being processed in a datastep or procedure. Two of the more least known are FIRSTOBS= and OBS=, both used as options and are defined as follows:
The following is an example of their usage, selecting observations 10 though to 30 from the dataset SUBANALYSIS for processing in the PRINT procedure: title1 "Listing of Obs. 10 to 30 of the SUBANALYSIS "
"Dataset for Testing Purposes";
proc print data=subanalysis (FIRSTOBS=10 OBS=30);
run;
As the example implies these options are best used for Development, Testing or Quality Control purposes so as to reduce time for processing – more often than not all data is processed for production reporting purposes. MarchWhen a SAS dataset is sorted and is later being processed, SAS creates a FIRST and LAST flag for each BY variable with a value of 1 if true and 0 if false. SAS sets the FIRST.variable when it is processing the first observation in a BY group, and sets the LAST.variable when it is processing the last observation in a BY group. This allows for different actions, based on whether processing is starting for a new BY group or ending for a BY group. The following example will output all records for a customer (CUSTNUM) from data master dataset (MNTHTRNS) to a dataset (RPTTRANS) if they have two or more transactions in the one day (TRANDATE): DATA rpttrans;
SET mnthtrns;
BY custnum trandate;
IF ^(FIRST.trandate=LAST.trandate=1) THEN OUTPUT rpttrans;
RUN;
AprilI had a company come to me a few months ago and ask me to import some data in to a SAS dataset from an Excel spreadsheet. Quite simple you may think but this request had one small wrinkle - the spreadsheet had cell comments that also had to be imported in to a comment field. After a little bit of time and research on Excel and VBA I would like to share with you the VBA code that was used to bring the cell comments to a specified cell in Excel: Function GetCellCommentText(rCellComment As Range)
On Error Resume Next
GetCellCommentText = WorksheetFunction.Clean(rCellComment.Comment.Text)
On Error GoTo 0
End Function
To enter the VBA code into Excel select Tools > Macro > Visual Basic Editor then Insert > Module Then paste the code above and then press File > Close and Return to Microsoft Excel The function is now ready for use within Excel and can be accessed from the User Defined function list. Now you may be asking how did I run this new function from SAS and import the data into a SAS dataset? For that answer you will have to wait for later in the year. MayThis month sees the start of the main SAS Conference Season. The big three are:
Why goto one? There are three good reasons for going. First you get to meet other people who are interested in working with SAS and sharing what they do with the product. The second is that you meet representatives from SAS who showcase the lastest software products and their thinking of the future. Third is that you can actually meet the people who write the code behind SAS. If you cannot be there in person remember that you can see the highlights via the SAS website. As well as these major conferences there are a large number of local SAS User Groups spread thoughout the world who have their own meetings and support network - you can find their contact information from SAS via the SAS web site or by contacting SAS direct. Please support your local SAS User Group. JuneIn the computing world there are two main character sets used for representation of characters, ASCII and EBCDIC. The most common among PCs is ASCII, but EBCDIC is used in a number of mainframe systems. For the purposes of this tip I will restrict myself to the ASCII standard character set (codes 0 to 127) - codes 32 to 126 are standard characters, while codes 0-31 and 127 are non-printable characters. The following shows the printable characters (there are tables out there on the web that gives you the full set): 50 data _null_;
51 do i=32 to 126;
52 x=byte(i);
53 put i +(-1) '=' x ' ' @;
54 if mod(i-32+1,10)=0 then put;
55 end;
56 run;
32= 33=! 34=" 35=# 36=$ 37=% 38=& 39=' 40=( 41=)
42=* 43=+ 44=, 45=- 46=. 47=/ 48=0 49=1 50=2 51=3
52=4 53=5 54=6 55=7 56=8 57=9 58=: 59=; 60=< 61==
62=> 63=? 64=@ 65=A 66=B 67=C 68=D 69=E 70=F 71=G
72=H 73=I 74=J 75=K 76=L 77=M 78=N 79=O 80=P 81=Q
82=R 83=S 84=T 85=U 86=V 87=W 88=X 89=Y 90=Z 91=[
92=\ 93=] 94=^ 95=_ 96=` 97=a 98=b 99=c 100=d 101=e
102=f 103=g 104=h 105=i 106=j 107=k 108=l 109=m 110=n 111=o
112=p 113=q 114=r 115=s 116=t 117=u 118=v 119=w 120=x 121=y
122=z 123={ 124=| 125=} 126=~
Note that code 32 is a space, not a null. In the code above, the BYTE function was used to get the character from the ASCII code, for example code 65 is 'A'. But what if we wanted to know the reverse, that is what is the ASCII code for 'A'? In this case we would use the RANK function, as shown in the following example: 57 data _null_;
58 char=rank('A');
59 put char=;
60 run;
char=65
JulyThe three big SAS conferences are over for another year and it is now back to work and use what has been learnt. If you were not there SAS will start making available the conference proceedings shortly. Your local SAS User group will also be probably presenting highlights of the conferences so I urge you to support your local group. I receive a number of Excel spreadsheets each month from organizations with data to review. Some of these worksheets have highlighted cells. But how do you find the cells highlighted in a worksheet that has approximately 5000 rows? One way is to determine the highlight color of the cell using the following VBA code: Function showColorCode(rcell)
showColorCode = rcell.Interior.ColorIndex
End Function
To enter the VBA code into Excel select Tools > Macro > Visual Basic Editor then Insert > Module Then paste the code above and then press File > Close and Return to Microsoft Excel The function is now ready for use within Excel and can be accessed from the User Defined function list. The codes returned from the function will show the background color of the cell. Microsoft unfortunately did not keep codes consistent across different releases of Excel. The following list is a general guide that is useful when referring to color codes for Excel 97:
AugustSAS, like most other computer languages, use mathematical operators. The following table lists the most common operators used along with their definition and an example. Note that some operators have two or more forms, for example the Logical Not operator that can use either the "^", "~" or "NOT" symbols.
SeptemberYour program has a variable that is coded as 1=YES and 2=NO with an associated format called YN. Now there is a new value of 3=DON'T KNOW in your data but the format YN has not been changed. In your reporting program you can create another format, in this example YNX, using the following code: proc format;
value ynx
1='YES'
2='NO'
3="DON'T KNOW";
run;
A better way though, avoiding transcription problems, is to make the new YNX format by nesting the old format YN into the new as shown in the following code: proc format;
value ynx
3="DON'T KNOW"
other=[yn.];
run;
Note that you must enclose the existing format name in square brackets, as shown above, or with parentheses and vertical bars, for example (|yn.|). OctoberWant to find out if a macro variable exists or not in your program? The following macro will let you know: %macro mpresent (_var=, /*Macro variable to search for*/
_xists=_xists /*Macro variable returning the result - default _XISTS*/
);
%global &_Xists;
%let xdsid=%sysfunc(open(sashelp.vmacro (where=(name="%upcase(&_var)"))));
%let &_Xists=%SYSFUNC(ATTRN(&xdsid,any));
%let xrc=%sysfunc(close(&xdsid));
%mend mpresent;
The result of the find will be in the macro variable defined in the _XISTS parameter, 1 if it is present, any other value if it is not found. An example of the call is given in the following code fragment: %if %upcase(&dsetname)=DEMO %then %do;
data demoage;
set demo;
%mpresent(_var=sortlist,_xists=_xsort);
%if (&_xsort=1) %then by &sortlist;
...
run;
%end;
In the example the dataset DEMOAGE is created from the dataset DEMO by variables defined in the macro variable SORTLIST but only if the macro variable SORTLIST exists. This macro is one of my old favorites. Enjoy. NovemberHere is something from my vault that would like to share with you. It is an example where SAS can produce unexpected answers and has been known to fool even the most experienced SAS programmer. First, lets look at our three datasets, each indicating the number and color of all the jellybeans they found in a single packet of sweets: DATASET:TOM
Color Number Multiple
-----------------------
RED 1 N
YELLOW 3 Y
GREEN 2 Y
BLUE 0 N
DATASET:DICK
Color Number
-------------
RED 4
YELLOW 2
GREEN 3
BLUE 1
DATASET:HARRY
Color Number Multiple
-----------------------
RED 2 Y
YELLOW 4 Y
GREEN 3 Y
BLUE 1 N
The variable MULTIPLE in the datasets TOM and HARRY indicate that there were multiple counts for that color, thus when the data is brought together the variable will have to be set in DICK via the program. Below is a first attempt at bringing the data together: data all0;
length name $5;
set tom (in=a) dick (in=b) harry (in=c);
select;
when(a) name='TOM';
when(b) do;
name='DICK';
if number>1 then multiple='Y';
if missing(multiple) then multiple='N';
end;
when(c) name='HARRY';
end;
run;
When the program logic is reviewed, the datastep should bring in the data from datasets TOM, DICK and HARRY. If the data from dataset DICK is being processed then the MULTIPLE variable should be set to Y if NUMBER>1, and if the MULTIPLE is still missing then MULTIPLE should be set to N. Lets look at what the dataset ALL0 actually contains: name color number multiple
-------------------------------------
TOM RED 1 N
TOM YELLOW 3 Y
TOM GREEN 2 Y
TOM BLUE 0 N
DICK RED 4 Y
DICK YELLOW 2 Y
DICK GREEN 3 Y
DICK BLUE 1 Y
HARRY RED 2 Y
HARRY YELLOW 4 Y
HARRY GREEN 3 Y
HARRY BLUE 1 N
Look closely at value in the variable MULTIPLE for the BLUE count from DICK, it is 'Y' when the logic says it should be 'N'! This is due to a little known feature in SAS where if a variable is being set that is not in the original dataset (but is present in other datasets called in the SET statement) the variable will be treated as if a RETAIN statement was in force! To get around this be sure that you always explicitly define what should be set to a variable for all possibilities. In the case above, all the program would need is the line if missing(multiple) then multiple='N'; changed to read else multiple='N'; and the datastep would produce what was expected. DecemberI was asked a short time ago why use the SAS SUM function when adding two numbers a simple arithmetic expression is possible? To give a simple answer it is the way SAS deals with a 'missing' value. To use an example the following timesheet data for a project is loaded using the following datastep and two variable TOT_SUM and TOT_ARITH are created: data TimeSheet;
infile cards;
input DAY $ 1-3 AM_HOURS 5-8 PM_HOURS 10-13;
TOT_SUM=sum(AM_HOURS,PM_HOURS);
TOT_ARITH=AM_HOURS+PM_HOURS;
cards;
....
The variable TOT_SUM is created from adding the variables AM_Hours and PM_Hours using the SUM function while the variable TOT_ARITH is created from the using an arithmetic expression. The following table shows the results: DATASET:TimeSheet
Day AM_Hours PM_Hours Tot_Sum Tot_Arith
-------------------------------------------
MON 3.5 4.5 8.0 8.0
TUE . 1.0 1.0 .
WED 2.5 . 2.5 .
THU . . . .
FRI 2.0 3.0 5.0 5.0
From the table the variable TOT_SUM is correct as it considers the 'missing' value as a valid value in the expression. Have a safe and happy holiday season. |