topic Re: Constructing Tables in SAS Programming

Constructing Tables

RandyStan — Sat, 21 Sep 2019 18:50:46 GMT

Dear All

My Data is as follows

ID_B	ID_S	VarA
A	B	10
A	C	10
C	D	10
C	A	10
D	B	15
A	A	5
C	B	10
B	B	20
A	C	10

The total of VarA is 100

I want to construct the following two tables
The first with the raw numbers

	A	B	C	D
A	5	10	20	0
B	0	20	0	0
C	10	10	0	10
D	0	15	0	0

And Then I want the Table in Percentages

	A	B		C	D

A	0.05	0.1	0	0.2	0
B	0	0.2		0	0
C	0.1	0.1	0	0	0.1
D	0	0.15		0	0

Thanks in advance

Randy

Re: Constructing Tables

Reeza — Sat, 21 Sep 2019 19:01:59 GMT

Do you need data sets or displayed reports as your output?

PROC FREQ will do this, have you tried that so far, and if so, post what code you did try and let us know what didn't work.

Are your percents, percent of total, row or column?

Can you provide your data set as data steps?

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

@RandyStan wrote:

Dear All

My Data is as follows

ID_B ID_S VarA

A B 10

A C 10

C D 10

C A 10

D B 15

A A 5

C B 10

B B 20

A C 10

The total of VarA is 100

I want to construct the following two tables
The first with the raw numbers

A B C D

A 5 10 20 0

B 0 20 0 0

C 10 10 0 10

D 0 15 0 0

And Then I want the Table in Percentages

A B C D

A 0.05 0.1 0 0.2 0

B 0 0.2 0 0

C 0.1 0.1 0 0 0.1

D 0 0.15 0 0

Thanks in advance

Randy

Re: Constructing Tables

yabwon — Sat, 21 Sep 2019 19:46:30 GMT

As I wrote ons SAS-L, try this one:

ods html;
data have;
input
ID_B $        ID_S  $       VarA;
cards;
A               B              10
A               C              10
C               D              10
C               A              10
D               B              15
A               A                5
C               B              10
B               B              20
A               C              10
;
run;
proc print;
run;

proc format;
value blankaszero
. = 0
other = [best32.]
;

picture pctfmt
. = "0" (noedit)
low-high='9.99' (multipl=1)
;
run;


proc tabulate data = have ;
class id_:;
var varA;
table id_B=" ", id_s=" "*varA=" "*sum=" "*f=blankaszero. ;

table id_B=" ", id_s=" "*varA=" "*reppctsum=" "*f=pctfmt12.;
run;

All the best

Bart

Re: Constructing Tables

Ksharp — Sun, 22 Sep 2019 12:30:19 GMT

Do you want a table or just a report ?

data have;
input
ID_B $        ID_S  $       VarA;
cards;
A               B              10
A               C              10
C               D              10
C               A              10
D               B              15
A               A                5
C               B              10
B               B              20
A               C              10
;
run;
proc summary data=have nway;
class id_b id_s;
var vara;
output out=temp sum=;
run;

options missing='0';
proc transpose data=temp out=table1(drop=_:);
by id_b;
id id_s;
var vara;
run;
data table2;
 set table1;
 array x{*} _numeric_;
 do _n_=1 to dim(x);
   x{_n_}=x{_n_}*.01;
 end;
run;

Re: Constructing Tables

Watts — Sun, 22 Sep 2019 16:11:45 GMT

proc freq data=have;

     /* table displays only frequencies */
     tables ID_B * ID_S / nopct norow nocol;

     /* table displays only percents */
     tables ID_B * ID_S / nofreq norow nocol;

     weight VarA;

run;

Re: Constructing Tables

hashman — Mon, 23 Sep 2019 03:30:24 GMT

@RandyStan:

If you want reports, follow the advice of @yabwon or @Watts.

If you want files, the solution by @Ksharp will work. Alternatively, if you want to save some I/O and solve in 2 passes through the input file (cannot be done in just one), consider:

data have ;                                                                                                                                                                                                                                                     
  input (ID_B ID_S) (:$1.) VarA ;                                                                                                                                                                                                                               
  cards ;                                                                                                                                                                                                                                                       
A  B  10                                                                                                                                                                                                                                                        
A  C  10                                                                                                                                                                                                                                                        
C  D  10                                                                                                                                                                                                                                                        
C  A  10                                                                                                                                                                                                                                                        
D  B  15                                                                                                                                                                                                                                                        
A  A   5                                                                                                                                                                                                                                                        
C  B  10                                                                                                                                                                                                                                                        
B  B  20                                                                                                                                                                                                                                                        
A  C  10                                                                                                                                                                                                                                                        
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
proc sql noprint ;                                                                                                                                                                                                                                              
  select unique id_s into :v separated by " " from have ;                                                                                                                                                                                                       
quit ;                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                
data _null_ ;                                                                                                                                                                                                                                                   
  array v &v ;                                                                                                                                                                                                                                                 
  if _n_ = 1 then do ;                                                                                                                                                                                                                                          
    dcl hash h () ;                                                                                                                                                                                                                                             
    h.definekey  ("id_b") ;                                                                                                                                                                                                                                     
    h.definedata ("id_b") ;                                                                                                                                                                                                                                     
    do over v ;                                                                                                                                                                                                                                                
      h.definedata (vname (v)) ;                                                                                                                                                                                                                               
    end ;                                                                                                                                                                                                                                                       
    h.definedone () ;                                                                                                                                                                                                                                           
  end ;                                                                                                                                                                                                                                                         
  set have end = lastobs ;                                                                                                                                                                                                                                      
  vsum ++ vara ;                                                                                                                                                                                                                                                
  if h.find() ne 0 then call missing (of v[*]) ;                                                                                                                                                                                                               
  do over v ;                                                                                                                                                                                                                                                  
    if vname (v) = id_s then v ++ vara ;                                                                                                                                                                                                                      
  end ;                                                                                                                                                                                                                                                         
  h.replace() ;                                                                                                                                                                                                                                                 
  if lastobs ;                                                                                                                                                                                                                                                  
  h.output (dataset:"table1") ;                                                                                                                                                                                                                                 
  dcl hiter hi ("h") ;                                                                                                                                                                                                                                          
  do while (hi.next() = 0) ;                                                                                                                                                                                                                                    
    do over v ;                                                                                                                                                                                                                                                
      v = divide (v, vsum) ;                                                                                                                                                                                                                                  
    end ;                                                                                                                                                                                                                                                       
    h.replace() ;                                                                                                                                                                                                                                               
  end ;                                                                                                                                                                                                                                                         
  h.output (dataset:"table2") ;                                                                                                                                                                                                                                 
run ;

Kind regards

Paul D.