SAS NSW User Group

1. SAS NSW User Group Hints and Tips

2. SNUG Todays tips Working with Formats Implementing “Look Ahead” Functionality The COLON Operator Delete macro programs no longer needed Illustrate the IFC and IFN functions The UNIQUE function The ANYSPACE function Rounding times using the ROUND function

3. SNUG Working with formats Confused whether to use a format or in format.  These simple rules may help:

4. 0 1 0 0 0 520 21JUL2013 Fred 2 2 1 0 0 320 04MAY2012 John 3 3 0 1 0 650 21JUN2013 Maximillian 4 1 1 0 0 560 10APR2012 Alfie 5 2 1 0 0 360 21FEB2013 Brian 6 1 1 0 0 500 30MAR2013 Shazza 7 2 0 0 1 350 04SEP2012 Muriel data TEMPDATA; input id $1 @3 visit @5 treat1 @7 treat2 @9 treat3 @11 cost @18 _date date9. @28fname$15.; cards; datatempdata2 ; attrib id length = 8; settempdata(rename=(id=_id)); dollar = put(cost,dollar11.2); date = put (_date,date9.); id = input(_id,8.); run;

5. Working with formats Numeric values have been converted to character values Character values have been converted to numeric values Formats can take either character or numeric input.  They always return character output. Informats always take character input.  They can return character or numeric output. If your final variable, i.e. the variable you’re assigning, needs to be numeric, you need an informat. If you don’t follow the above rules, you’ll get implicit type conversion, which may not yield the desired. Rules to remember date = put (_date,date9.); @18 _date date9. dollar = put(cost,dollar11.2); date = put (_date,date9.);

6. snug Look ahead functionality • Need to make a decision on the current observation, based on the next observation • There are several ways to do this.  However, the below approach performs very well, so this approach is useful for large datasets. • There are easier ways to look behind (either the lag function or a retain statement).

7. procsortdata=sashelp.classout=class; by age name; run; * look ahead and behind ; data test1; formatlag_name name lead_name; **** This will be the current record ****; set class; **** the previous record. This will always be one record behind ****; if _n_ > 1then set class (keep=name rename=(name=lag_name)) ; **** the next record. This will always be one record ahead ****; **** need to check eof to prevent premature end to the data step****; **** if we forget to set the call missing(lead_name) the last ****; **** record will be repeated. ****; ifnoteofthen set class (firstobs=2 keep=name rename=(name=lead_name))end=eof; else call missing(lead_name); run;

8. data test2; formatlag_name name lead_name; **** This will be the current record ****; set class; **** driving set with current observations ****; by age; * the previous record. This will always be one record behind ; if _n_ > 1then set class (keep=name rename=(name=lag_name)); * the next record. This will always be one record ahead ; * need to check eof to prevent premature end to the data step ; ifnoteofthen set class (firstobs=2 keep=name rename=(name=lead_name)) end=eof; else call missing(lead_name); * we want lead and lag processing within a by group ; iffirst.agethen call missing(lag_name); iflast.agethen call missing(lead_name); run;

9. snug Colon operator The colon (:) plays many roles in SAS code. It can be used as 1. Label indicator 2. Format modifier 3. Operator modifier 4. Key word component 5. Variable name wildcard 6. Array bound delimiter 7. Argument feature delimiter 8. Special log indicator 9. Index creation operator

10. snug Colon operator data _names_JA _names_GE_JA; set class; ifsubstr(name,1,2) = 'Ja'then output _names_JA; elseifsubstr(name,1,2) > 'Ja'then output _names_GE_JA; run; Label Indicator The label Indicator is a statement referred to by a GOTO or a LINK statement data gender; attrib gender length= $8; set class; if sex = 'F'then goto female; elseif sex = 'M'then goto male; female: gender = 'Female'; return; male: gender = 'Male'; return; run; Operator Modifier & Special Log Indicator Allows a comparison of character strings. Inserts a user comment into the log; data _names_JA _names_GE_JA; set class; if name =:'Ja'then output _names_JA; if name >:'Ja'then output _names_GE_JA; run; %put WARNING: Stop and Check data is correct!; Can also put ERROR: NOTE: into log as well. WARNING: Stop and Check data is correct!

11. Variable or Dataset Name Wildcard A colon following a variable or dataset name prefix selects any variable whose name starts with that prefix procdatasetslib=work memtype=data nolist; delete _:; quit; • Key Word Component In PROC SQL, the colon is part of the “SELECT… INTO :” Structure procsqlnoprint; select name into :names separated by ', ' from class (where =(name >: 'RO')); quit; %put &names; NOTE: Deleting WORK._NAMES_GE_JA (memtype=DATA). NOTE: Deleting WORK._NAMES_JA (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds data tempdata2 (drop = _:); attrib id length = 8; settempdata(rename=(id=_id)); dollar = put(cost,dollar11.2); date = put (_date,date9.); id = input(_id,8.); run; We get the macro variable values Thomas, Robert, Ronald, William

12. Snug Replacing macros in catalogs • You will always need to overwrite the current macro in a catalog with the newest version while developing or updating. • What methods can we use to do it? • You can overwrite it • Change may not be reflected in current session • Delete and replace it. • proccatalogcatalog= work.sasmac1; • deletemacroname /et=macro; • quit;

13. Snug Replacing macros in catalogs proccatalogcatalog= work.sasmac1; delete hint /et=macro; quit; ET = ENTRYTYPE

14. Snug Ifc And ifn • Every patient has three treatment visits. • The effect of the treatment at each visit is measured • 1 means effective; 0 means not effective • The cost for each patient is also recorded.

15. snug Ifc and ifn procsqlnoprint; select trim(name) into :treatlist separated by ', ' fromsashelp.vcolumn wherelibname="WORK" andmemname = 'TEMPDATA2' andupcase(name) like'TREAT%'; quit; procsql; select id ,sum(&treatlist) as efficacy ,ifc(max(calculated efficacy) = 1, 'Yes', 'No') as effective length = 3 ,sum(cost) assum_cost format = dollar10.2'Total cost' ,ifn(calculated sum_costge1000 ,calculated sum_cost*0.85 ,calculated sum_cost*1) asdiscounted_cost format=dollar10.2'Total cost after discount' from tempdata2 groupby id; quit;

16. snug Ifc and ifn procsql; select id ,sum(&treatlist) as efficacy ,case calculated efficacy when0then 'NO ' else'YES' endas effective length = 3 ,sum(cost) assum_cost format = dollar10.2'Total cost' ,case when calculated sum_cost >= 1000then calculated sum_cost*0.85 else calculated sum_cost endasdiscounted_cost format=dollar10.2'Total cost after discount' from tempdata2 groupby id ; quit;

17. Unique Function This function is very convenient to show the number of the levels for every categorical variable. procsql; select count(distinct(make)) asu_make'Number of the car makers' ,count(distinct(origin)) asu_origin'Number of the car origins' ,count(distinct(type)) asu_type'Number of the car types' fromsashelp.cars; quit;

18. snug Anyspace function Searches a character string for a white-space character (blank, horizontal and vertical tab, carriage return, line feed, and form feed), and returns the position at which that character is found.

19. Changing to spaces • Check converted data anyspace2; setanyspace; j=0; dountil(j=0); j=anyspace(name,j+1); **** when we get to the end of the record ****; if j=0then put +3"No Further spaces found"; elsedo; c=put(substr(name,j,1),$hex5.) ; **** Put values to log for review ****; put +3 j= c= ; **** replace non printable character with a space ****; substr(name,j,1) = ' '; end; end; run; data anyspace2; set anyspace2; j=0; dountil(j=0); j=anyspace(name,j+1); **** when we get to the end of the record ****; if j=0then put +3"No Further spaces found"; elsedo; c=put(substr(name,j,1),$hex5.) ; **** Put values to log for review ****; put +3 j= c= ; **** replace non printable character with a space ****; end; end; run; This section includes table-specific design decisions. Each table is on a separate page.

20. snug Rounding times Most people use the round function to round numbers to the nearest decimal. Did you know we can also do it for time values as well?

21. snug Rounding times Data temp; attribdtlabel= 'Raw datetime'format=DATETIME. dt2 label= 'Datetime to 15 minutes'format=DATETIME. dt3 label= 'Datetime to 7 1/2 minutes'format=DATETIME. dt4 label= 'Datetime to 15 seconds'format=DATETIME. ; **** create a datetime - 2:23pm ****; dt = dhms('12mar2003'D,14,23,10); **** round it to nearest 15 minutes ****; dt2 = Round(dt,hms(0,15,00)); **** round it to nearest 7 1/2 minutes;****; dt3 = Round(dt,hms(0,7,30)); **** to the nearest 15 seconds ****; dt4 = Round(dt,hms(0,0,15)); output; RUN;

22. snug Rounding times Example showing how to round times using datetime constants. Data temp; attribdtlabel= 'Raw datetime'format=DATETIME. dt2 label= 'Datetime to 15 minutes'format=DATETIME. dt3 label= 'Datetime to 7 1/2 minutes'format=DATETIME. dt4 label= 'Datetime to 15 seconds'format=DATETIME. ; **** create a datetime - 2:23pm ****; dt = '12mar2003:14:23:10'DT; **** round it to nearest 15 minutes ****; dt2 = Round(dt,'0:15:00'T); **** round it to nearest 7 1/2 minutes;****; dt3 = Round(dt,'0:7:30'T); **** to the nearest 15 seconds ****; dt4 = Round(dt,'0:00:15'T); Put (dt dt2 dt3 dt4) (DATETIME.' '); RUN;

23. Thank you