The SAS Output Delivery System and reporting techniques

PUT PERCENT FORMULA IN PERTICULAR CELL

Reply
Contributor
Posts: 22

PUT PERCENT FORMULA IN PERTICULAR CELL

Hello,

I need solution in the following table

Current

CountACount%COUNTBcount %_CUM
10010101004545
2001052003015
10045454005012.5
400656070012572.5 total

DESIRED

CountACount%COUNTBcount %_CUM
10010101004545
2001052003015
10045454005012.5
4006516.2570012517.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:

CountACount%COUNTBcount %_CUM
100101010045.
20010520030.
100454540050.
4006516.25700125. total

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

Thanks.

SAS Super FREQ
Posts: 8,743

PUT PERCENT FORMULA IN PERTICULAR CELL

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

Contributor
Posts: 22

PUT PERCENT FORMULA IN PERTICULAR CELL

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

CountACount%COUNTBcount%_CUM
100101010045.
20010520030.
100454540050.
4006516.25700125. total
PROC Star
Posts: 7,363

PUT PERCENT FORMULA IN PERTICULAR CELL

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.


Contributor
Posts: 22

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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

PROC Star
Posts: 7,363

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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.

PROC Star
Posts: 7,363

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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;

Contributor
Posts: 22

PUT PERCENT FORMULA IN PERTICULAR CELL

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 :

Untitled.png

Thanks

PROC Star
Posts: 7,363

PUT PERCENT FORMULA IN PERTICULAR CELL

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.

Contributor
Posts: 22

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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.

Contributor
Posts: 22

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

Table will look following way:








CUM








EMPLOYEE

SP
CODEDESCRIPTION    CMELIGCOUNT  CMHAPART           PCT(%)

      CMELIG

      COUNT

CMHAPARTPCT_CUM(%)CMELIGCOUNTCMHAPARTPCT_CUM(%)
100FORMER1055010550



200LABOR-143754375



300LABOR-252405240



400LABOR3133


3133
500LABOR320



20

GRAND TOTAL
241144191052.651                      20
PROC Star
Posts: 7,363

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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;

Regular Contributor
Regular Contributor
Posts: 166

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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

Contributor
Posts: 22

Re: PUT PERCENT FORMULA IN PERTICULAR CELL

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.

Ask a Question
Discussion stats
  • 13 replies
  • 331 views
  • 3 likes
  • 4 in conversation