Thanks in advance for your help on my question!
Here is how the data looks like now (Now), and how I need to change it (Required) only using proc report
Now | ||
| orange | purple |
aaa | 1 | 1 |
bbb | . | 2 |
ccc | 2 | . |
Required | ||
| orange | purple |
aaa | 1+5 | 1+5 |
bbb | 5 | 2+5 |
ccc | 2+5 | 5 |
data
test;
input
type $ color $ counter;
cards
;
aaa purple 1
aaa orange 1
bbb purple 2
ccc orange 2
;
run
;
If possible I would like to use proc report, however I can't use the method below, by hardcoding the colums (in the real case the exact position of the coulmn varies)
proc
report nowd data=test missing ;
COLUMN
type color, (counter counter=num);
define type / group ' ';
define
color / across ' ';
define
counter /sum ' ' noprint;
define
num / sum ' ' nozero;
compute
num;
_C3_ = _C2_+
5;
_C5_ = _C4_+
5;
endcomp
;
run
;
Since Proc Report doesn't do well with relative columns I would say your best bet would be to fix the data not the report and possibly change Option missing='.' to Option missing='5'; but the logic of problem is some what obscure. You might want to expand on the example data a bit to make the logic of the manipulation clearer.
Your code doesn't work either, you would need
compute num;
_C3_ = sum(_C2_,5);
_C5_ = sum(_C4_,5);
endcomp;
to add 5 to the missing and get 5.
Since Proc Report doesn't do well with relative columns I would say your best bet would be to fix the data not the report and possibly change Option missing='.' to Option missing='5'; but the logic of problem is some what obscure. You might want to expand on the example data a bit to make the logic of the manipulation clearer.
Your code doesn't work either, you would need
compute num;
_C3_ = sum(_C2_,5);
_C5_ = sum(_C4_,5);
endcomp;
to add 5 to the missing and get 5.
Thank you ballardw!
I just made up an e.g. (but I forgot about having entered missing values, thank you for drawing me he attention)
All I wanted to find out was, a way to write the proc report, where the values that are going to be displayed across, in this case the 'counter' , could be altered (changed based on any algorithm, in this case increased by 5).
If this will be possible in proc report, then I would not have to 'fix' the data, as the data is created in a macro. (this macro creates many different datasets, except for one where additional manipulation is needed; I was trying to avoid having to add this additional data step and use proc report for this purpose)
Thanks again for your advice!
So where 5 is coming from ? Isn't it 6 ? as ballardw said it is not good for proc report .
Why not use a macro variable hold it and assign it by hard code, as you showed ?
data test;
input type $ color $ counter;
cards;
aaa purple 1
aaa orange 1
bbb purple 2
ccc orange 2
;
run;
proc report data=test nowd ;
columns type color,counter dummy;
define type/group;
define color/across;
define dummy/computed noprint;
compute before;
sum1=_c2_;
sum2=_c3_;
endcomp;
compute dummy;
_c2_=sum(sum1,sum2,_c2_);
_c3_=sum(sum1,sum2,_c3_);
endcomp;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.