🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

i want to creata a table

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Obsidian | Level 7

Re: i want to creata a table

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 ;``````
9 REPLIES 9
Garnet | Level 18

Re: i want to creata a table

There are endless number of tables having thus "magic" rule.

What is your input format and value to start with ?

Quartz | Level 8

Re: i want to creata a table

my input format is a= 1, b=2, c=3

Garnet | Level 18

Re: i want to creata a table

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;

Quartz | Level 8

Re: i want to creata a table

no thats the part of the input
Ammonite | Level 13

Re: i want to creata a table

what is the format value for DD and for EE?

Quartz | Level 8

Re: i want to creata a table

if a= 1 b = 2 c= 3 then if i make a table it for a and b it should be a multiple of both a and b
the value for d will be 4 and e will be 5
Obsidian | Level 7

Re: i want to creata a table

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 ;``````
Garnet | Level 18

Re: i want to creata a table

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);

``````

Quartz | Level 8

Re: i want to creata a table

thank you so much
Discussion stats
• 9 replies
• 1289 views
• 0 likes
• 4 in conversation