Help using Base SAS procedures

How to hide date variable used to calculate new column in Proc Report?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How to hide date variable used to calculate new column in Proc Report?

I am attempting to compute a new date variable to format the original date variable into a different format. The value of the 'orig_update_dt' column is 25JUL2000:00:00:00.

I get the correct results for both columns 'orig_update_dt' & 'new_update_dt' as long as I use 'display' on the DEFINE statement. The results are:

'orig_update_dt' = 25JUL2000:00:00:00

'new_update_dt' =  07/25/2000

However, if I change the DEFINE statement on 'orig_update_dt' and use 'noprint' instead of 'display', the computed variable 'new_update_dt' does not compute.  The value of 'new_update_dt is 01/01/1960 when the 'display' is changed to 'noprint'.  I know this is the SAS base date so I guess it means it is not computing the new variable for some reason.  I am pretty new to this so am I missing something?

my code:

COLUMN orig_update_dt new_update_dt

DEFINE   orig_update_dt  / display ;

DEFINE   new_update_dt / display computed format=mmddyys10.;                                                                                                   <= if

COMPUTE  new_update_dt;

                  new_update_dt=(datepart(orig_update_dt));

ENDCOMP;


Accepted Solutions
Solution
‎12-08-2012 12:11 PM
SAS Super FREQ
Posts: 8,868

Re: How to hide date variable used to calculate new column in Proc Report?

Posted in reply to ncsthbell

Hi:

  Thanks for referencing the course notes! It's a good course for understanding how to use both TABULATE and REPORT. If you look elsewhere in Chapter 4 I think that your issues can be clarified.

  First, look at the USAGES -- DISPLAY, COMPUTED, ANALYSIS, ORDER, GROUP and ACROSS are USAGES (see pages 4-24 and 4-25), where it talks about USAGE options vs ATTRIBUTE options). If you have a numeric variable and you replace DISPLAY with NOPRINT, you are getting rid of the USAGE option and therefore, the numeric variable will get a default usage of ANALYSIS SUM. It is always a best practice to ask yourself how you want a variable to be USED on the report. And then we recommend that you put your USAGE option explicitly on the DEFINE statement. You have to pick from DISPLAY, COMPUTED, ORDER, GROUP, ACROSS or ANALYSIS (with your statistic of choice) as the USAGE option. Pick only 1 USAGE option. The option ANALYSIS is implied if you use a statistic name. So these are ALL equivalent statements:

DEFINE   orig_update_dt  / analysis sum noprint f=datetime.;

DEFINE   orig_update_dt  / sum noprint f=datetime.;

DEFINE   orig_update_dt  / analysis noprint f=datetime.;

DEFINE   orig_update_dt  / noprint f=datetime.;  /* default USAGE is ANALYSIS */

  So the last DEFINE statement (in pink) is the equivalent of what you described. By removing DISPLAY, you essentially told PROC REPORT to summarize ORIG_UPDATE_DT and use the variable for analysis.

  NOPRINT is an ATTRIBUTE OPTION, not a USAGE OPTION -- they are NOT the same type of option. If you have a USAGE of ANALYSIS and the SUM statistic, then this changes the way that you must refer to a variable in a COMPUTE block. On page 4-73, what is the USAGE for SALARY? It is ANALYSIS (with the default SUM statistic). How is BONUS calculated in the program? BONUS was calculated as BONUS=SALARY.SUM * 0.035;

  Look on page 4-27 underneath slide 57 at note that shows 2 reports. One report (on the left) has only SALARY on the report -- with no DEFINE statement. Without any other variable on the report and with a numeric variable in the COLUMN statement, the default usage for SALARY is ANALYSIS with the SUM statistic. So, note how SALARY is collapsed to 1 cell. I would imagine that something similar to report #7 (in the screenshot) is what is happening to your report when you get rid of DISPLAY for ORIG_UPDATE_DT.

  Finally, look at page 4-68 -- it shows the 3 statements that work together to make a COMPUTED column on the report. 1) list the new item in the COLUMN statement; 2) make a DEFINE statement with the USAGE option of COMPUTED (and since an item can only have 1 USAGE option, there is no DISPLAY COMPUTED) on that slide; 3) use your assignment statement in a COMPUTE block.

  Then, on page 4-70, look at the way SALARY.SUM is explained "Variable references inside your compute block must follow PROC REPORT rules." is the beginning of the explanation. Then on page 4-71, there is a chart that shows when and how you would use a compound name. All of those pieces of information lead up to the NOPRINT example on page 4-73. It doesn't say anywhere that NOPRINT is a USAGE option or that NOPRINT replaces a USAGE option. You use NOPRINT to HIDE a report column. You use a USAGE option - -either GROUP, ORDER, DISPLAY or ANALYSIS sum with ORIG_UPDATE_DT to tell PROC REPORT how you want the variable treated. I would probably have used ORDER or DISPLAY.

  BTW, a variable or report item can only have 1 USAGE -- so DISPLAY COMPUTED is technically wrong for your COMPUTED variable. If you are going to use a COMPUTE block to calculate NEW_UPDATE_DT, then DISPLAY is irrelevant to what you're doing. And, the use of ORDER=INTERNAL is shown on page 4-38, with a note that ORDER= specified the order for the report item (if it has an ORDER or GROUP variable usage) And then on page 4-39, on the syntax model for using ORDER=, we show a USAGE of ORDER as the model. There is not a usage of DISPLAY shown as the syntax model.

So,  your problem was probably not with NOPRINT, your problem was probably caused by not understanding how a USAGE option is specified on the DEFINE Statement and how it had an impact on the way you reference a variable in a COMPUTE block.

  The screenshot attached show some of the 7 outputs produced by the program below. (I didn't bother showing NOPRINT -- but you could run the attached program to see the impact of NOPRINT.) I made more than 1 row of fake data, just to illustrate some of the differences, especially in how ORDER changed the order of the rows. And, I made a TYPECAT variable just because with only 2 variables on the report, it would look odd to NOPRINT on one of the items and just leave 1 item on the report. It just seemed likely to me that you would have other variables on the report.

cynthia


data dateval;
typecat='aaa';
orig_update_dt = '25JUL2000:00:00:00'dt;
output;
orig_update_dt = '16JAN2000:00:00:00'dt;
output;
orig_update_dt = '20APR2000:00:00:00'dt;
output;
run;
   
ods listing close;
ods html file='c:\temp\dateval.html' style=sasweb;
proc report data=dateval nowd;
title '1) Display Usage';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / display  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '2) Display Usage with NOPRINT';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / display noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '3) Analysis Usage Needs compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / analysis sum f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;

    

proc report data=dateval nowd;
title '4) Analysis Usage with NOPRINT till needs compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / analysis sum noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '5) Order Usage does not need compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / order  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '6) Order Usage with NOPRINT does not need compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / order noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '7) If only numeric variables and ANALYSIS with SUM usage -- will be WRONG (summary) report';
COLUMN orig_update_dt new_update_dt;
DEFINE   orig_update_dt  / analysis sum  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;
ods html close;



incorrect_usage_date_var.png

View solution in original post


All Replies
Super User
Posts: 10,044

Re: How to hide date variable used to calculate new column in Proc Report?

Posted in reply to ncsthbell

You can add an option to check the detail what REPORT did .

proc report data=x nowd out=temp ;

...........

For the numeric type variable, report defaultly use ANALYSIS usage that means only calculate the sum for this variable, therefore you should use DISPLAY usage to force it to display as a detail report.

The better is to post some sample data and the result you need ,that will elaborate your question more .

Ksharp

Frequent Contributor
Posts: 82

Re: How to hide date variable used to calculate new column in Proc Report?

Thank you for your reply.  My sample data in the column 'orig_update_dt' is 25JUL2000:00:00:00

I put the options on the proc report you suggested 'nowd out=temp'.  What I see in results in 'temp' is the display of the data results in column format.  I am using the 'display' option on both the original data coming in the column

'orig_update_dt' and the new computed column 'new_update_dt'.

This seems so simplistic.  In the SAS Training manual "SAS Report Writing 1: Using Procedures and ODS" on page 4-73 there is a section called 'Hiding Report Columns'. This is what is says: To hide the variables that are used to calculate the new column value, use the NOPRINT option in the DEFINE for each variable you want to hide."   Since this is referring to 'variables', I assume this means of any data type including a date datatype.  This column is defined in the input as 'DATETIME20." 

This is exactly what I am doing. I am using the variable 'orig_update_dt' to compute the formatted date into the variable 'new_update_dt' and then using 'NOPRINT' instead of 'DISPLAY' on the variable 'orig_update_dt'.  When I change DISPLAY to NOPRINT to hide it, the date will not compute in the 'new_update_dt' variable.  However, if I leave DISPLAY instead of NOPRINT, the variable 'new_update_dt' is computed correctly.  


Solution
‎12-08-2012 12:11 PM
SAS Super FREQ
Posts: 8,868

Re: How to hide date variable used to calculate new column in Proc Report?

Posted in reply to ncsthbell

Hi:

  Thanks for referencing the course notes! It's a good course for understanding how to use both TABULATE and REPORT. If you look elsewhere in Chapter 4 I think that your issues can be clarified.

  First, look at the USAGES -- DISPLAY, COMPUTED, ANALYSIS, ORDER, GROUP and ACROSS are USAGES (see pages 4-24 and 4-25), where it talks about USAGE options vs ATTRIBUTE options). If you have a numeric variable and you replace DISPLAY with NOPRINT, you are getting rid of the USAGE option and therefore, the numeric variable will get a default usage of ANALYSIS SUM. It is always a best practice to ask yourself how you want a variable to be USED on the report. And then we recommend that you put your USAGE option explicitly on the DEFINE statement. You have to pick from DISPLAY, COMPUTED, ORDER, GROUP, ACROSS or ANALYSIS (with your statistic of choice) as the USAGE option. Pick only 1 USAGE option. The option ANALYSIS is implied if you use a statistic name. So these are ALL equivalent statements:

DEFINE   orig_update_dt  / analysis sum noprint f=datetime.;

DEFINE   orig_update_dt  / sum noprint f=datetime.;

DEFINE   orig_update_dt  / analysis noprint f=datetime.;

DEFINE   orig_update_dt  / noprint f=datetime.;  /* default USAGE is ANALYSIS */

  So the last DEFINE statement (in pink) is the equivalent of what you described. By removing DISPLAY, you essentially told PROC REPORT to summarize ORIG_UPDATE_DT and use the variable for analysis.

  NOPRINT is an ATTRIBUTE OPTION, not a USAGE OPTION -- they are NOT the same type of option. If you have a USAGE of ANALYSIS and the SUM statistic, then this changes the way that you must refer to a variable in a COMPUTE block. On page 4-73, what is the USAGE for SALARY? It is ANALYSIS (with the default SUM statistic). How is BONUS calculated in the program? BONUS was calculated as BONUS=SALARY.SUM * 0.035;

  Look on page 4-27 underneath slide 57 at note that shows 2 reports. One report (on the left) has only SALARY on the report -- with no DEFINE statement. Without any other variable on the report and with a numeric variable in the COLUMN statement, the default usage for SALARY is ANALYSIS with the SUM statistic. So, note how SALARY is collapsed to 1 cell. I would imagine that something similar to report #7 (in the screenshot) is what is happening to your report when you get rid of DISPLAY for ORIG_UPDATE_DT.

  Finally, look at page 4-68 -- it shows the 3 statements that work together to make a COMPUTED column on the report. 1) list the new item in the COLUMN statement; 2) make a DEFINE statement with the USAGE option of COMPUTED (and since an item can only have 1 USAGE option, there is no DISPLAY COMPUTED) on that slide; 3) use your assignment statement in a COMPUTE block.

  Then, on page 4-70, look at the way SALARY.SUM is explained "Variable references inside your compute block must follow PROC REPORT rules." is the beginning of the explanation. Then on page 4-71, there is a chart that shows when and how you would use a compound name. All of those pieces of information lead up to the NOPRINT example on page 4-73. It doesn't say anywhere that NOPRINT is a USAGE option or that NOPRINT replaces a USAGE option. You use NOPRINT to HIDE a report column. You use a USAGE option - -either GROUP, ORDER, DISPLAY or ANALYSIS sum with ORIG_UPDATE_DT to tell PROC REPORT how you want the variable treated. I would probably have used ORDER or DISPLAY.

  BTW, a variable or report item can only have 1 USAGE -- so DISPLAY COMPUTED is technically wrong for your COMPUTED variable. If you are going to use a COMPUTE block to calculate NEW_UPDATE_DT, then DISPLAY is irrelevant to what you're doing. And, the use of ORDER=INTERNAL is shown on page 4-38, with a note that ORDER= specified the order for the report item (if it has an ORDER or GROUP variable usage) And then on page 4-39, on the syntax model for using ORDER=, we show a USAGE of ORDER as the model. There is not a usage of DISPLAY shown as the syntax model.

So,  your problem was probably not with NOPRINT, your problem was probably caused by not understanding how a USAGE option is specified on the DEFINE Statement and how it had an impact on the way you reference a variable in a COMPUTE block.

  The screenshot attached show some of the 7 outputs produced by the program below. (I didn't bother showing NOPRINT -- but you could run the attached program to see the impact of NOPRINT.) I made more than 1 row of fake data, just to illustrate some of the differences, especially in how ORDER changed the order of the rows. And, I made a TYPECAT variable just because with only 2 variables on the report, it would look odd to NOPRINT on one of the items and just leave 1 item on the report. It just seemed likely to me that you would have other variables on the report.

cynthia


data dateval;
typecat='aaa';
orig_update_dt = '25JUL2000:00:00:00'dt;
output;
orig_update_dt = '16JAN2000:00:00:00'dt;
output;
orig_update_dt = '20APR2000:00:00:00'dt;
output;
run;
   
ods listing close;
ods html file='c:\temp\dateval.html' style=sasweb;
proc report data=dateval nowd;
title '1) Display Usage';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / display  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '2) Display Usage with NOPRINT';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / display noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '3) Analysis Usage Needs compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / analysis sum f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;

    

proc report data=dateval nowd;
title '4) Analysis Usage with NOPRINT till needs compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / analysis sum noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '5) Order Usage does not need compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / order  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '6) Order Usage with NOPRINT does not need compound name';
COLUMN typecat orig_update_dt new_update_dt;
define typecat / order;
DEFINE   orig_update_dt  / order noprint  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt));
ENDCOMP;
run;

   

proc report data=dateval nowd;
title '7) If only numeric variables and ANALYSIS with SUM usage -- will be WRONG (summary) report';
COLUMN orig_update_dt new_update_dt;
DEFINE   orig_update_dt  / analysis sum  f=datetime.;
DEFINE   new_update_dt /  computed
         format=mmddyys10.; 
COMPUTE  new_update_dt;
        new_update_dt=(datepart(orig_update_dt.sum));
ENDCOMP;
run;
ods html close;



incorrect_usage_date_var.png
Frequent Contributor
Posts: 82

Re: How to hide date variable used to calculate new column in Proc Report?

Posted in reply to Cynthia_sas

Cynthia, thank you for your reply!  I really appreciate you taking the time to explain the different options for usage & display, etc.  Your information was a GREAT help!!  I have changed my progam code and the date is now formatted correctly.

Thanks again!  Great start for a Monday for me!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 2594 views
  • 1 like
  • 3 in conversation