Hi Everyone. I use this site extensively, so thank you in advance for all of the questions you have answered already. This is my first occasion to write a question of my own. In attempting to create columns that will return missing/zero counts in PROC TABULATE, I have run across the technique of substituting a PRELOADFMT dataset where the analysis set returns zero counts but does not display the column. I have had problems with this technique because my analysis set generates variable values for specific dates that are tabulated based on when the report is run. My reports include work accomplished last week, last month, and last year; all terminating last Saturday. ACTIONDT is the variable stating when the action took place in the dataset. I use a loop to assign each date the work was accomplished to the appropriate time period for each date column used in the reporting. That is, I dynamically generate date variables' values based on existing data. I require the values as formatted for reporting purposes as I could not determine how to assign it thusly to a value labels. Here is how I do it: data SASUSER.STEP002 ;
set SASUSER.STEP001 ;
/** ROW_VALUE = Set value for tabulation **/
/** ACTIONS = Initialize variable with a value to be set to undefined **/
ROW_VALUE = 1 ;
ACTIONS = 0 ;
STARTDATE = intnx('year',today(),0,'b') ; /*\ start date for analysis is first day of the year \*/
ENDDATE = intnx('week',today(),-1,'e') ; /*\ end date is the end of last week \*/
LOOPDATE = STARTDATE ; /*\ loop set to start date for project analysis \*/
LOOPEND = intck('day',STARTDATE,ENDDATE) ; /*\ used to set the number of loops \*/
WEEKNM = week(STARTDATE) ; /*\ returns number of week within year \*/
MNTHNM = month(STARTDATE) ; /*\ returns number of month wihtin year \*/
WEEKDT = '31Dec2999'd ; /*\ set to create noticeable outlier if not recoded \*/
format STARTDATE ENDDATE LOOPDATE WEEKDT ACTIONDT mmddyys10. ;
/*****************************************************************************/
/** LOOP FOR DATE CODING PURPOSES. Loop runs from StartDate to EndDate by **/
/** using INTCK value for loop terminus. **/
/*****************************************************************************/
data SASUSER.STEP002 ;
set SASUSER.STEP002 ;
DO I = 0 TO LOOPEND BY 1 ; /*\ Loop advances days by one from Start to End Date \*/
IF ACTIONDT = ( LOOPDATE + I ) THEN WEEKNM = week( ACTIONDT ) ; /*\ Generates week number based on Action Out Date \*/
IF ACTIONDT = ( LOOPDATE + I ) THEN WEEKDT = intnx('week',ACTIONDT,0,'b') ; /*\ Generates date of beginning of week \*/
IF ACTIONDT = ( LOOPDATE + I ) THEN MNTHNM = month(ACTIONDT ) ; /*\ Generates month number based on Action Out Date \*/
END ;
run ; The problem I have is in creating a PRELOADFMT substitute dataset. I cannot generate the same dates as in the analysis dataset because I lack the dates in the substitute set upon which the PROC TABULATE variables are based. DO WEEKDT = value1, value2, value3, etc... ; I do not have all of the values, which I think is key to my problem. I have tried to create them dynamically based on just today's date and starting at the beginning of last year, but I cannot determine how to assign the values to a variable in a DO LOOP as part of the creation of the substitute dataset (here referred to as WORK.ALTERNATE). proc tabulate MISSING CLASSDATA=WORK.ALTERNATE ;
title2 "Last Week" ;
var ROW_VALUE ;
class USER ACTIONS FORM_NUMBER WEEKDT / preloadfmt order=data ;
table ( USER ALL = 'Total' ) ,
( WEEKDT * FORM_NUMBER = ' ' ) *
( ACTIONS = ' ' ALL = 'Total' )
( ALL = 'Grand Total' )
/ printmiss ;
where intnx('week',today(),-1,'b') <= WEEKDT <= intnx('week',today(),-1,'e') ;
footnote1 "The week runs Sunday through Saturday." ;
run ; Obviously, I am looking for suggestions on how to tackle this problem. To state it succinctly: I need to create a dataset that recreates all categorical values possible in the analysis dataset. This is being done to return zero counts so that dropped columns might be displayed in PROC TABULATE. I appreciate all suggestions. For reference purposes, I am using SAS EG 7.11.
... View more
