topic Re: PUT PERCENT FORMULA IN PERTICULAR CELL in ODS and Base Reporting

PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Sun, 25 Dec 2011 02:34:17 GMT

Hello,

I need solution in the following table

Current

CountA	Count	%	COUNTB	count	%_CUM
100	10	10	100	45	45
200	10	5	200	30	15
100	45	45	400	50	12.5
400	65	60	700	125	72.5 total

DESIRED

CountA	Count	%	COUNTB	count	%_CUM
100	10	10	100	45	45
200	10	5	200	30	15
100	45	45	400	50	12.5
400	65	16.25	700	125	17.85 total

I used the following code.

ODSTAGSE.excel.xp OPTION(

);

DATA x;

MERGE A B;

By VAR;

If a or b then do;

percent=Count/COUNTB;

end;

run;

PROC FORMAT;

proc format;

value $relation '000'='Employee'

'001'='Spouse / Domestic Partner';

run;

PROC REPORT DATA=x nowd split='*'

Colume (COUNTA Count %) (RELATION(COUNTB Count %));

DEFINE COUNTA / analysis sum;

DEFINE Count / analysis sum;

DEFINE COUNTB / Analysis;

Define count / analysis;

DEFINE % / analysis ;

DEFINE %_CUM / Analysis;

rbreak after / summarize skip;

Compute after;

call Deifne (_ROW_, STYLE );

endcomp;

run;

I also try following code;

Data x;

Merge A B;

By Var;

run;

PROC REPORT DATA nowd split="*"

Colume (COUNTA Count %) (RELATION(COUNTB Count %));

DEFINE COUNTA / analysis sum;

DEFINE Count / analysis sum;

DEFINE COUNTB / Analysis;

Define count / analysis;

DEFINE % / computed format = percent4.;

DEFINE %_CUM / computed format= percet4.;

compute %;

%=count.sum / COUNTA ;

endcomp;

compute %_CUM;

%_cum=count.sum / COUNTB;

endcomp;

rebreak after/ summarize skip suppress;

Compute after;

%=count.sum / COUNTA ;

%_cum=count.sum / COUNTB;

Call define= (_ROW_,STYLE);

endcomp;

run;

RESULT:

CountA	Count	%	COUNTB	count	%_CUM
100	10	10	100	45	.
200	10	5	200	30	.
100	45	45	400	50	.
400	65	16.25	700	125	. total

This create missing value in %_CUM woth out any error or notes in LOG phase.

Thanks.

PUT PERCENT FORMULA IN PERTICULAR CELL

Cynthia_sas — Sun, 25 Dec 2011 17:21:34 GMT

Hi:

I am very curious that your code works -- the reference to % and %_CUM should be generating error messages, because they violate the naming conventions for SAS variables (must start with a letter or underscore and continue with any combination of letters, numbers and underscores). In addition, the reference to %_CUM could be mistaken for a call to a macro program, which would also generate warnings in the log.

For example, I ran a simple test using the % and %_CUM in my code and got a LOT of error messages, as shown below.

cynthia

12758 proc report data=sashelp.class nowd;

12759 column age sex n height weight % %_cum;

200

WARNING: Apparent invocation of macro _CUM not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', -, :, =, _ALL_,

_CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored.

12760 define age / group;

12761 define sex / group;

12762 define n / 'Count';

12763 define height / sum;

12764 define weight / sum;

12765 define % / computed 'computed %';

200

12766 define %_cum / computed 'computed cum %';

200

WARNING: Apparent invocation of macro _CUM not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, CSS, CV, MAX, MEAN, MEDIAN,

MIN, MODE, N, NMISS, P1, P10, P25, P5, P50, P75, P90, P95, P99, PCTN, PCTSUM,

PROBT, PRT, Q1, Q3, QRANGE, RANGE, STD, STDERR, SUM, SUMWGT, T, USS, VAR, _ALL_,

_CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored.

12767 compute %;

200

ERROR 22-322: Syntax error, expecting one of the following: a name, AFTER, BEFORE.

ERROR 200-322: The symbol is not recognized and will be ignored.

12768 %=height.sum/weight.sum;

12769 endcomp;

12770 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE REPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Sun, 25 Dec 2011 18:16:47 GMT

Hi Cynthia,

Ok I corrected code and put following code:

PROC REPORT DATA nowd split="*"

Colume (COUNTA Count PCT) (RELATION(COUNTB Count PCT_CUM));

DEFINE COUNTA / analysis sum;

DEFINE Count / analysis sum;

DEFINE COUNTB / Analysis;

Define count / analysis;

DEFINE PCT / "%" computed format = percent4.;

DEFINEPCT_CUM "%" computed format = percent4.;

compute PCT;

PCT=count.sum / COUNTA ;

endcomp;

compute %_CUM;

PCT_cum=count.sum / COUNTB;

endcomp;

rebreak after/ summarize skip suppress;

Compute after;

PCT=count.sum / COUNTA ;

PCT_cum=count.sum / COUNTB;

Call define= (_ROW_,STYLE);

endcomp;

run;

But still It create same table

CountA	Count	%	COUNTB	count	%_CUM
100	10	10	100	45	.
200	10	5	200	30	.
100	45	45	400	50	.
400	65	16.25	700	125	. total

PUT PERCENT FORMULA IN PERTICULAR CELL

art297 — Sun, 25 Dec 2011 20:14:12 GMT

I would think that the table shown was simply left over from the previous run.

What do your two tables really look like and what are you trying to accomplish with the datastep? If both A and B have a variable with the same name (e.g., count), then you will have to rename it when merging the files.

In your step you say if a or b: what are those supposed to stand for? If it is whether data came from file a or file b you would have had to have used the in= option in order to set those variables. But, if you did, the if then statement would still always be applied. I'm not at all sure what you are trying to do.

DATA x;

MERGE A B;

By VAR;

If a or b then do;

percent=Count/COUNTB;

end;

run;

In your proc report statement, similarly, you define count twice! And what do Colume, and DEFINEPCT_CUM represent. Are they typos?

I think you have to provide the forum with more, and more carefully presented, info.

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Sun, 25 Dec 2011 20:25:47 GMT

Sorry art,

Here is the correct code:

DATA x;

MERGE A(in=a) B (in=b);

By VAR;

If a or b then do;

PCT=Count/COUNTA;

PCT_CUM=Count/COUNTB;

end;

run;

PROC FORMAT;

proc format;

value $relation '0'='Employee'

'1'='Spouse';

run;

PROC REPORT DATA=x nowd split='*'

Column (COUNTA Count PCT) (RELATION,(COUNTB Count PC_CUM));

DEFINE COUNTA / analysis sum;

DEFINE Count / analysis sum;

DEFINE COUNTB / Analysis;

DEFINE Realtion/Across f=$relation.;

DEFINE PCT / analysis ;

DEFINE PCT_CUM / Analysis;

rbreak after / summarize skip;

Compute after;

call Deifne (_ROW_, STYLE );

endcomp;

run;

Thanks

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

art297 — Sun, 25 Dec 2011 20:39:36 GMT

I and others will still have the same problems.

What do your 2 files really look like and how do you want to treat the two different instances of the variable count? In short, after running the datastep, what do you expect the resulting file to look like before it is input into proc report?

What kind of computer are you using? On windows,Count and count will be treated as being the same variable.

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

art297 — Sun, 25 Dec 2011 21:19:54 GMT

Does the following correctly describe what your data look like and the resulting file that you are trying to use proc report to print?:

data a;

input var CountA Count;

cards;

1 100 10 10

2 200 10 5

3 100 45 45

4 400 65 60

;

data b;

input var COUNTB count;

cards;

1 100 45 45

2 200 30 15

3 400 50 12.5

4 700 125 72.5

;

DATA x;

MERGE A (in=a) B (in=b rename=(count=count2));

format pct pct_cum percent8.5;

By VAR;

If a then PCT=Count/COUNTA;

if b then PCT_CUM=Count2/COUNTB;

run;

PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Sun, 25 Dec 2011 21:39:38 GMT

Art,

Dataset is look like:

data ABC;

input DESCRIPTION TOTAL_COUNT REALTIONSHIP;

cards;

ABC 100 Male

CDE 200 FEMALE

;

run;

data CDE;

input DESCRIPTION REMAIN_COUNT REALTIONSHIP;

cards;

ABC 40 Male

CDE 50 FEMALE

;

run;

I need to creat following table :

Thanks

PUT PERCENT FORMULA IN PERTICULAR CELL

art297 — Sun, 25 Dec 2011 23:16:04 GMT

I'd really like to help but can't figure out what you are trying to do. Now, instead of files a and b, you have files abc and cde and, again, no variable 'var' which you use as your by variable.

Additionally, I have no idea where the various numbers come from for your desired table.

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Mon, 26 Dec 2011 04:09:36 GMT

Hello,

Ok. Here is two datasets. An I want to create table .

data TEST;

input Relationship CODE $ DESCRIPTION $ CMELIGCOUNT;

cards;

1 100 FORMER 10

2 300 LABOR-1 3

1 300 LABOR-1 4

1 400 LABOR-2 5

2 400 LABOR-2 2

;

RUN;

data TEST2;

input Relationship CODE $ DESCRIPTION $ CMHAPART;

cards;

1 100 FORMER 5

2 300 LABOR-1 1

1 300 LABOR-1 3

1 400 LABOR-2 2

2 400 LABOR-2 0

;

RUN;

PROC SORT DATA = TEST;

BY RELATIONSHIP;

run;

PROC SORT DATA = TEST2;

BY RELATIONSHIP;

run;

Data TEST3;

Merge TEST(in=a) TEST2(in=b);

if a or b;

By Relationship;

run;

PROC REPORT DATA= TEST3;

Column CODE DESCRIPTION ("CUMULATIVE"((CMELIGCOUNT=total CMHAPART PCT)(RELATIONSHIP,(CMELIGCOUNT=CWELIGCOUNT CMHAPART=CWHAPART PCT_CUM))));

DEFINE CODE/Display left;

DEFINE DESCRIPTION/DISPLAY left;

DEFINE total/ analysis sum right f=comma6.;

DEFINE CMHAPART/"COUNT" analysis sum right f=comma6.;

DEFINE PCT/ "%" COMPUTED right format=4.;

DEFINE RELATIONSHIP/across;

DEFINE CWELIGCOUNT/ analysis sum right f=comma4.;

DEFINE PCT_CUM/ "%" COMPUTED right format=percent4.;

DEFINE CWHAPART/ analysis sum right f=comma4.;

rbreak after / summarize skip suppress;

compute PCT;

PCT=(CMHAPART.sum/TOTAL)*100;

endcomp;

compute PCT_CUM;

PCT_CUM=(CMHAPART.sum/TOTAL)*100;;

endcomp;

Compute after;

CODE="GRAND TOTAL";

DESCRIPTION="";

PCT=(CMHAPART.sum/total)*100;

PCT_CUM=(CMHAPART.sum/TOTAL)*100;

endcomp;

run;

When I am running this code Percent column ( PCT_CUM) create missing value. In log phase it doesn't create any error.

Thanks.

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Mon, 26 Dec 2011 04:16:35 GMT

Table will look following way:

							CUM
						EMPLOYEE			SP
CODE	DESCRIPTION	CMELIGCOUNT	CMHAPART	PCT(%)	CMELIG COUNT	CMHAPART	PCT_CUM(%)	CMELIGCOUNT	CMHAPART	PCT_CUM(%)
100	FORMER	10	5	50	10	5	50
200	LABOR-1	4	3	75	4	3	75
300	LABOR-2	5	2	40	5	2	40
400	LABOR	3	1	33				3	1	33
500	LABOR3	2	0					2	0
GRAND TOTAL		24	11	44	19	10	52.6	5	1	20

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

NN — Mon, 26 Dec 2011 18:37:42 GMT

Hi,

I cannot test your code right now but please check if what i say works....

In your proc report statement just add an out= and run the code once

PROC REPORT DATA= TEST3 out=check_file;

View the data set check_file

Since you have used an across variable i think you would be able to see certan columns like _C6_ , _C7_

So in your proc report your compute block should look something like

compute pct_cum;

_c8_ = _c7_ / _c6_;

_c11_ = _c10_ / _c9_;

endcomp;

And also i think you Donot need to repeat your calculation in the COMPUTE AFTER block.

Hope it helps

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

art297 — Mon, 26 Dec 2011 18:45:28 GMT

Before you even get to the report part, I think you may want to include your code variable in your merge. i.e.,

PROC SORT DATA = TEST;

BY RELATIONSHIP code;

run;

PROC SORT DATA = TEST2;

BY RELATIONSHIP code;

run;

Data TEST3;

Merge TEST(in=a) TEST2(in=b);

if a or b;

By Relationship code;

run;

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

patel1987 — Mon, 26 Dec 2011 18:45:33 GMT

Hi,

Thanks I tried your option and it works. but if I didnt put in repeate COMPUTE BLOCK then it doesnt count in GRAND TOTAL ROW.

Thanks.