topic Re: i want to creata a table in SAS Programming

i want to creata a table

Aayushi_17 — Fri, 14 Dec 2018 18:33:36 GMT

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.

Re: i want to creata a table

Shmuel — Fri, 14 Dec 2018 15:33:12 GMT

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

What is your input format and value to start with ?

Re: i want to creata a table

Aayushi_17 — Fri, 14 Dec 2018 18:35:46 GMT

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

Re: i want to creata a table

Shmuel — Fri, 14 Dec 2018 16:10:12 GMT

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;

Re: i want to creata a table

Aayushi_17 — Fri, 14 Dec 2018 16:02:49 GMT

no thats the part of the input

Re: i want to creata a table

VDD — Fri, 14 Dec 2018 16:06:03 GMT

what is the format value for DD and for EE?

Re: i want to creata a table

Aayushi_17 — Fri, 14 Dec 2018 18:37:33 GMT

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

Re: i want to creata a table

johnsville — Fri, 14 Dec 2018 22:23:07 GMT

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 ;

Re: i want to creata a table

Shmuel — Sat, 15 Dec 2018 05:27:08 GMT

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

Re: i want to creata a table

Aayushi_17 — Sun, 16 Dec 2018 06:06:24 GMT

thank you so much