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.
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
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 |
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.
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
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.
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;
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
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.
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.
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 |
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: