PUT PERCENT FORMULA IN PERTICULAR CELL

[patel1987]

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.

[Cynthia_sas]

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;

                                        -

                                        22

                                        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 %';

                -

                22

                200

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

                -

                22

                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 %;

                 -

                 22

                 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

[patel1987]

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

[art297]

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.

[patel1987]

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

[art297]

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.

[art297]

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;

[patel1987]

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

[art297]

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.

[patel1987]

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.

[patel1987]

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

[art297]

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;

[NN]

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

[patel1987]

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.