topic Re: Creating a table from multiple tables in SAS Programming

Creating a table from multiple tables

mcook — Thu, 25 Jun 2020 17:56:32 GMT

I have several tables of just one variable.

Table 1:

MasterList

A
B
C
D
E
F
G

Table2:

Var1

Table3:

Var2:

with the rest similar. With each table having some or all of the values in MasterList.

I want to create a table with the variables (var1, var2, etc) as the observations, and the columns A, B,....,G from MasterList.

where the observation has a value of 1 if it contains that element from MasterList, and 0 if it does not.

Like so.

A B C D E F G

Var1 1 1 0 0 1 0 1

Var2 0 1 1 0 1 1 0

Var3 ...

any help appreciated.

Re: Creating a table from multiple tables

yabwon — Thu, 25 Jun 2020 19:02:31 GMT

Hi,

maybe like this (a view, format, tabulate, and a text file 😉

data Table1;
input MasterList $;
cards;
A
B
C
D
E
F
G
;
run;

data Table2;
input Var1 $;
cards;
A
B
E
G
;
run;
 

data Table3;
input Var2 $;
cards;
B
C
E
F
;
run;

data TMP/view = TMP;
  merge table:;
  array _X_(i) _character_;

  do over _X_;
    _name_ = vname(_X_);
    _value_ = _X_;
    output;
  end;
  keep _name_ _value_;
run;

proc format;
  value bool
  0,. = 0
  other = 1
  ;
run;

data _null_;
  set table1 end = eof;
  length list $ 1000;
  retain list;
  list = catx(" ", list, MasterList);
  if eof;
  call symputx("list", list, "G");
run;

ods _all_ close;
options ps = max ls = max nocenter nodate nonumber formchar="";
title;
filename f TEMP;
proc printto print = f;
run;
proc tabulate data = TMP;
  class _name_ _value_;
  table _name_=" ", _value_=" "*n=" "*f=bool.;
run;
proc printto;
run;
ods html;

data want;
  infile f firstobs = 6;
  input @@;
  put _infile_;
  if _infile_ ne "" then
    do;
      input var_name : $ 32. &list.;
      output;
    end;
  else input;
run;
proc print;
run;

All the best

Bart

Re: Creating a table from multiple tables

mcook — Thu, 25 Jun 2020 19:11:15 GMT

Wow. unfortunately that seems much more complicated and beyond my current understanding of SAS. Might there be a different way?

Re: Creating a table from multiple tables

Shmuel — Thu, 25 Jun 2020 19:45:57 GMT

You can use next code:

data MasterList;
  infile cards;
  input key $;
cards;
A                                    
B
C
D
E
F
G
; run;

data table2;
  infile cards;
  input key $;
cards;
A                                    
B
E
G
; run;

data table3;
  infile cards;
  input key $;
cards;
B                                    
C
E
F
; run;

%macro assign;
   select (key);
    when ('A')  i=1;
	when ('B')  i=2;
    when ('C')  i=3;
	when ('D')  i=4;
    when ('E')  i=5;
	when ('F')  i=6;
	when ('G')  i=7;
	otherwise put '***Error ' key=;
   end;
   x(i) = 1;
%mend assign;

proc sql noprint;
  select key into :list1 separated by ' '
  from MasterList
  ;
  select key into :list2 separated by ','
  from MasterList
  ;
quit;

data want;
     retain &list1;
     drop i key;
     
     array x {*} &list1;
	 do i=1 to dim(x); x(i)=0; end;

  /* any non last table */	 
	 do until (eof);
	    set table2 end=eof;
		   %assign;
		   if eof then output;
	 end;
	 do i=1 to dim(x); x(i)=0; end; eof=0;

  /* last table */	 
	 do until (eof);
	    set table3 end=eof;
		   %assign;
		   if eof then output;
	 end;
	 
run;

Re: Creating a table from multiple tables

yabwon — Thu, 25 Jun 2020 19:55:58 GMT

Hi,

try this:

data TMP/view = TMP;
  merge table:;
  array _X_(i) _character_;

  do over _X_;
    _nm_ = vname(_X_);
    _value_ = _X_;
    output;
  end;
  keep _nm_ _value_;
run;

proc tabulate data = TMP out = o(keep = _nm_ _value_ N);
  class _nm_ _value_;
  table _nm_=" ", _value_=" "*n=" ";
run;

proc transpose data = o out = want(drop = _name_);
  by _nm_;
  var N;
  id _value_;
run;

data want;
  set want;
  array N _numeric_;
  do over N;
    N = (N > .);
  end;
run;

Bart

Re: Creating a table from multiple tables

r_behata — Thu, 25 Jun 2020 20:13:47 GMT

data Table1;
input MasterList $;
cards;
A
B
C
D
E
F
G
;
run;

data Table2;
input Var1 $;
cards;
A
B
E
G
;
run;

data Table3;
input Var2 $;
cards;
B
C
E
F
;
run;

data _null_;
	length vlst $25.;
	set Table1 end=done;
	retain vlst ' ';
	vlst=catx(' ',vlst,MasterList);
	put _all_;

	if done then
		call symputx('vlst',vlst);
run;

%put &vlst.;

data Test;
	array mlist[*] &vlst.;
	set Table2 Table3(rename=(var2=var1)) indsname=dsname;

	do i=1 to dim(mlist);
		if vname(mlist[i])=var1 then
			mlist[i]=1;
		else mlist[i]=0;
	end;

	varname=transtrn(scan(dsname,2),'TABLE','VAR');
	Drop i Var1;
run;

proc means data=test nway missing noprint;
	class varname;
	var &vlst.;
	output out= want(drop=_:) sum=;
run;

Re: Creating a table from multiple tables

Shmuel — Thu, 25 Jun 2020 20:24:05 GMT

Alternatively and simplified code will be:

proc sql noprint;
  select key into :list1 separated by ' '
  from MasterList
  ;
quit;

data want;
     retain &list1;
     cx = compress("&list1");
     drop i key cx;
     
     array x {*} &list1;
	 do i=1 to dim(x); x(i)=0; end;

  /* any non last table */	 
	 do until (eof);
	    set table2 end=eof;
		   i = indexc(cx,key);
		   x(i) = 1;
		   if eof then output;
	 end;
	 do i=1 to dim(x); x(i)=0; end; eof=0;

  /* last table */	 
	 do until (eof);
	    set table3 end=eof;
		   i = indexc(cx,key);
		   x(i) = 1;
		   if eof then output;
	 end;
run;

Re: Creating a table from multiple tables

Ksharp — Fri, 26 Jun 2020 13:02:27 GMT

data Table1;
input MasterList $;
cards;
A
B
C
D
E
F
G
;
run;

data Table2;
input Var1 $;
cards;
A
B
E
G
;
run;

data Table3;
input Var2 $;
cards;
B
C
E
F
;
run;

%macro tran(dsn=,var=);
data temp;
 set &dsn;
 _&var=1;
run;
proc transpose data=temp  out=_&dsn;
id &var;
var _&var;
run;
%mend;

%tran(dsn=table1,var=MasterList)
%tran(dsn=table2,var=var1)
%tran(dsn=table3,var=var2)

data all;
 set _: ;
run;
proc stdize data=all out=want reponly missing=0;
run;