SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aayushi_17
Quartz | Level 8

Hi all I intend to create table like this

xabc
a123
b246
c369

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
johnsville
Obsidian | Level 7

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 ;

View solution in original post

9 REPLIES 9
Shmuel
Garnet | Level 18

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

What is your input format and value to start with ?

Aayushi_17
Quartz | Level 8

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

Shmuel
Garnet | Level 18

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;

 

 

Aayushi_17
Quartz | Level 8
no thats the part of the input
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

what is the format value for DD and for EE?

Aayushi_17
Quartz | Level 8
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
johnsville
Obsidian | Level 7

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

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

 

Aayushi_17
Quartz | Level 8
thank you so much

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1620 views
  • 0 likes
  • 4 in conversation