- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi all I intend to create table like this
x | a | b | c |
a | 1 | 2 | 3 |
b | 2 | 4 | 6 |
c | 3 | 6 | 9 |
where in base we have a b and c and the values for a b c are given if we check them horizontally or vertically it should show the same value like a should be 1 and for a and b it should be 2 even in column and row .Please advise how to do it. Thanks.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
maybe this does what you need? After running, check table _final for validity. Add more vars in the first step's instream card file.
data _first ;
length str $32760 ;
retain str '' ;
infile datalines eof = eof ;
do;
input x $ value ;
str = catx(',' , str, strip(x) || ' * value as ' || strip(x) ) ;
drop str ;
output;
end ;
eof:
call symputx('cols', str) ;
datalines;
a 1
b 2
c 3
;
%put *** &=cols ;
proc transpose data=_first out = _t ;
var value;
id x ;
run ;
proc sql noprint ;
create table _all as
select x.*,y.*
from _first x , _t y
;
quit ;
proc sql noprint ;
create table _final as
select x , &cols
from _all ;
quit ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There are endless number of tables having thus "magic" rule.
What is your input format and value to start with ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
my input format is a= 1, b=2, c=3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
In your posted sample there is cc=6.
Is it part of the input? or should it be calculated by any formula?
What about bc &cb ?
Can you create your input using data step like:
data start;
infile cards;
input names $2, value;
cards;
aa 1
ab 2
ac 2
...
; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
what is the format value for DD and for EE?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
the value for d will be 4 and e will be 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
maybe this does what you need? After running, check table _final for validity. Add more vars in the first step's instream card file.
data _first ;
length str $32760 ;
retain str '' ;
infile datalines eof = eof ;
do;
input x $ value ;
str = catx(',' , str, strip(x) || ' * value as ' || strip(x) ) ;
drop str ;
output;
end ;
eof:
call symputx('cols', str) ;
datalines;
a 1
b 2
c 3
;
%put *** &=cols ;
proc transpose data=_first out = _t ;
var value;
id x ;
run ;
proc sql noprint ;
create table _all as
select x.*,y.*
from _first x , _t y
;
quit ;
proc sql noprint ;
create table _final as
select x , &cols
from _all ;
quit ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
On second looking, ignoring names, your table is:
1*1=1 1*2=2 1*3=3 2*1=2 2*2=4 2*3=6 3*1=3 3*2=6 3*3=9
based on this formula you can create any dimension table by next tested code:
%macro magic(names);
%let n = %sysfunc(countw(&names));
data want(keep=&names);
array ch $ c1-c&n;
array va {*} v1-v&n;
do i=1 to &n;
ch(i) = scan("&names",i);
va(i) = i;
end;
do i=1 to &n-1;
%do i=1 %to &n;
%scan(&names, &i) =
%if &i=1 %then ch(i+1);
%else va(i)*(&i-1);
;
%end;
output;
end;
run;
%mend magic;
options mprint;
%magic(x a b c d);
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content