topic Re: proc report, tabulate or another procedure? in SAS Procedures

proc report, tabulate or another procedure?

veblen — Wed, 27 Apr 2011 16:50:18 GMT

I am trying to generate the reports described below and not sure if I should use PROC REPORT, TABULATE or another procedure. (I seem to go down one path only to see than another might have advantages).

Of course I would like to complete it in as few "steps" as possible.

Any assistance would be highly appreciated.

Please let me know if any additional info is required.

Also, are there any hard and fast rules to determine what procedure to use in such reporting? Or is the selection primarily based on programmer choice? I would like to find a process than I can use/standardize across any organization.

Thanks, beforehand...

date - Var1 - Var2 - Var3
04-04 5 6 6
04-07 3 2 2
04-09 5 6 1
04-11 4 4 6
04-13 2 6 4
04-15 1 6 3
04-19 1 6 6

(note: only a single month of data is being shown.)

3 Reports:

* Total # Responses (greater than or equal to 3);
* % of Responses (greater than or equal to 3) of the Total;
* Month-to-month % change of Responses over 3.

(note: I want it to be flexible enough to handle additional reporting, when needed.)

------Jan - Feb - Mar - Apr -> Dec

Q1
Q2
Q3

Re: proc report, tabulate or another procedure?

Ksharp — Thu, 28 Apr 2011 05:32:45 GMT

Your posr is ambiguous.
You said want three report.
But What these report do look like? and What layout are these report.
En. I think Cynthia@sas will be happy to help you.

Ksharp

Re: proc report, tabulate or another procedure?

veblen — Thu, 28 Apr 2011 18:16:23 GMT

The three reports are to have the same basic format (see below).

Report 1: Includes the total # of responses (greater than or equal to 3) for each Var by month.
Report 2: Includes the % of responses (greater than or equal to 3) of the total for each Var by month.
Report 3: Includes the month-to-month % change of the responses (greater than or equal to 3) for each Var by month.

(note: I want it to be flexible enough to handle additional reporting, when needed.)

------Jan - Feb - Mar - Apr -> Dec

Var1
Var2
Var3

(note: I had Q1, Q2, Q3, listed for the Vars in my initial example.)

Re: proc report, tabulate or another procedure?

Reeza — Thu, 28 Apr 2011 18:54:29 GMT

Your going to have to do some calculations before you can do any reports, though you might be able to get away with proc report.

You posted the data you have, a sample of what the reports would look is useful otherwise we're trying to guess your calculations.

I'd suggest adding in a flag for each variable/month gt 3 or lt3 as an indicator.

Then you can use proc tabulate to sum orpercent that as required to get the first two reports easily.

The month to month change, are you comparing to the previous month? or same month previous year?

How do you define % change for gt 3 when that would be an indicator, it would basically be a change or no change? Message was edited by: Reeza

Re: proc report, tabulate or another procedure?

veblen — Thu, 28 Apr 2011 19:46:22 GMT

Thanks for the help Ksharp and Reeza, but I guess I will take it from here.

The calculations are not the issue as much as the generation of the monthly data.

I have provided the sample table format and I'm just looking to populate it with the totals and percentages by month.

Thanks Again.

Re: proc report, tabulate or another procedure?

Reeza — Thu, 28 Apr 2011 20:08:45 GMT

Monthly data isn't the issue for the first two definitions

see below.

For the month to month change you'd have to pre calculate the data for the reports so might as well do that anyways.

data have;
format date date9.;
input date mmddyy10. Var1 Var2 Var3;
cards;
04-04-2011 5 6 6
04-07-2011 3 2 2
04-09-2011 5 6 1
04-11-2011 4 4 6
04-13-2011 2 6 4
04-15-2011 1 6 3
04-19-2011 1 6 6
05-04-2011 5 6 6
05-07-2011 3 2 2
05-09-2011 5 6 1
05-11-2011 4 4 6
05-13-2011 2 6 4
05-15-2011 1 6 3
05-19-2011 1 6 6
06-04-2011 5 6 6
06-07-2011 3 2 2
06-09-2011 5 6 1
06-11-2011 4 4 6
06-13-2011 2 6 4
06-15-2011 1 6 3
06-19-2011 1 6 6
;
run;

data have2;
set have;

flag_var1=var1 >= 3;
flag_var2=var2 >= 3;
flag_var3=var3 >= 3;
run;

proc tabulate data=have2;
class date;
var flag_var1 flag_var2 flag_var3;
tables date='', sum=''*flag_var1 sum=''*flag_var2 sum=''*flag_var3;
format date monyy7.;
run;

Re: proc report, tabulate or another procedure?

Ksharp — Fri, 29 Apr 2011 05:20:50 GMT

OK.
[pre]

data have;
format date mmddyy5.;
input date : mmddyy10. Var1 Var2 Var3;
cards;
04-04-2011 4 6 2
04-07-2011 3 2 2
04-09-2011 3 6 1
04-11-2011 4 4 6
04-13-2011 2 2 4
04-15-2011 1 2 3
04-19-2011 2 2 2
05-04-2011 5 6 6
05-07-2011 3 2 4
05-09-2011 7 6 2
05-11-2011 4 4 6
05-13-2011 6 3 5
05-15-2011 2 6 3
05-19-2011 8 2 6
06-04-2011 2 2 3
06-07-2011 2 2 2
06-09-2011 3 6 1
06-11-2011 4 4 6
06-13-2011 5 3 4
06-15-2011 1 4 3
06-19-2011 1 6 2
;
run;

data want(keep= date var_name value flag month);
set have;
array v{*} var:;
do i=1 to dim(v);
var_name=vname(v{i});
value=v{i};
month=put(date,monname3.);
if value ge 3 then flag='Y';
else flag='N';
output;
end;
run;

ods listing close;
ods pdf file="c:\report1.pdf" style=sasweb notoc;
options nonumber nodate ;
title ' Total # Responses (greater than or equal to 3)';
proc report data=want(where=(flag='Y')) nowd;
column var_name date,value;
define var_name /group ' ';
define date/across format=monname3. order=internal ' ';
define value/analysis sum ' ';
run;
ods pdf close;
ods listing;

proc sort data=want;
by month var_name flag;
run;

data report2;
set want;
by month var_name;
if first.var_name then do;subtotal=0; total=0;end;
total+value;
if flag = 'Y' then subtotal+value;
if last.var_name then do;percent=subtotal/total;output;end;
run;

ods listing close;
ods pdf file="c:\report2.pdf" style=sasweb notoc;
options nonumber nodate ;
title '% of Responses (greater than or equal to 3) of the Total ';
proc report data=report2 nowd;
column var_name date,percent;
define var_name /group ' ';
define date/across format=monname3. order=internal ' ';
define percent/analysis format=percent8.2 ' ';
run;
ods pdf close;
ods listing;

proc sort data=report2;
by var_name date;
run;
data report3;
set report2;
by var_name;
dif_per=dif(percent);
if first.var_name then call missing(dif_per);
run;
proc format ;
picture per
low -< 0 ='09.99%' (prefix='-' mult=10000)
0 - high=[ percent8.2 ]
;
run;
ods listing close;
ods pdf file="c:\report3.pdf" style=sasweb notoc;
options nonumber nodate ;
title 'Month-to-month % change of Responses over 3 ';
proc report data=report3 nowd;
column var_name date,dif_per;
define var_name /group ' ';
define date/across format=monname3. order=internal ' ';
define dif_per/analysis format=per. ' ';
run;
ods pdf close;
ods listing;

[/pre]

Ksharp Message was edited by: Ksharp

Re: proc report, tabulate or another procedure?

veblen — Wed, 11 May 2011 04:13:53 GMT

My apologies Reeza and Ksharp; I thought that I had posted a thanks prior to going underwater.

So, first of all: THANK YOU both. Answering posts in such forums is a very generous and nice thing to do. I do it when I can and will definitely be posting in this forum from the answering side when my skills and confidence allow.

Ksharp: I like the use of arrays, they will go a long way solving some of my reporting needs.

What would the code below look like in order to handle vars that are named a1_1 to a1_7?

Also, I would look to it looping through a1, a2, a3 and a4.

(a1_1, a1_2, a1_3, a1_4, a1_5, a1_6, a1_7, a2_1, a2_2, a2_3, a2_4, a2_5, a2_6, a2_7)

data want(keep= date var_name value flag month);
set have;
array v{*} var:;
do i=1 to dim(v);
var_name=vname(v{i});
value=v{i};
month=put(date,monname3.);
if value ge 3 then flag='Y';
else flag='N';
output;
end;
run;

Re: proc report, tabulate or another procedure?

Ksharp — Wed, 11 May 2011 10:10:29 GMT

You can code like this:
[pre]
array v{*} a1_: a2_: a3_: a4_:

[/pre]

Or You also can define a range like this:
[pre]
array v{*} a1_1 -- a2_7;

[/pre]

a1_1 is your first variable in dataset and a2_7 is your last variable in dataset.

Ksharp

Re: proc report, tabulate or another procedure?

veblen — Thu, 19 May 2011 04:11:51 GMT

Thanks again for the array code Ksharp. It not only works well in the current task, but I see myself using it a lot.

I asked about using arrays in this form --- array v{*} a1_: a2_: a3_: a4_: --- but I neglected to ask how you handle the flags and reporting with multiple arrays. Or to put it another way I understand the input but not the output.

One last thing regarding the reports: What's the easiest way to run significance tests on the reports and highlight the results in the report?

Re: proc report, tabulate or another procedure?

Ksharp — Thu, 19 May 2011 10:05:07 GMT

to run significance tests ,you need to use proc univariate or proc means or proc ttest to get the p-value ,and merge it into report.
About highlight the results in the report, you can use traffic light format, Emmmmm,
I am very sure Cynthia@sas know it very well ,you can post it into ODS forum, and She will be happy to help you. Of course I can do it also . 🙂

Ksharp

Re: proc report, tabulate or another procedure?

veblen — Fri, 20 May 2011 00:41:40 GMT

thanks Ksharp. But all you had to do was say traffic lighting, the "I know" comment was unnecessary...:-)