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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.