How to create a table of 0s and 1s using proc sql

[UdayGuntupalli]

All, 
      How can I create a matrix/table of zero's and ones. I understand that matrices require package IML and I don't have that. So I would like to do it using Proc SQL if possible. Kindly advise. 

Best 

Uday 

[PaigeMiller]

Well, when you ask a general and vague question, providing no details whatsoever, the best we can provide is a general and vague answer.

proc sql;
    create table want as select x1,case when x1>100 then 1 else 0 end as zero_one from have;
quit; 

[UdayGuntupalli]

@PaigeMiller, 
        Let me try and provide some context. I am a JMP user who is learning SAS. What I would like to do is generate a matrix of 0's and 1's of a size of my choice. This can be done easily in JMP. (JMP is a product of SAS). 

Based on my understanding, SAS requires a package called IML to work with matrices. In the license that I am currently working with, I don't have the IML package and hence I would like to use Proc SQL to generate a table that can be used as a SAS dataset for my needs. I am looking for help on how to do this. 

[PGStats]

Create tha data in long form first with SQL, then transpose. Example:

proc sql;
create table long as
select 
    a.sex,
    a.name as row,
    b.name as column,
    a.age > b.age as zeroOne
from
    sashelp.class as a inner join
    sashelp.class as b on a.sex=b.sex
order by a.sex, row, column;
quit;

proc transpose data=long out=table0(drop=_name_);
by sex row;
id column;
var zeroOne;
run;

proc stdize data=table0 out=table missing=0 reponly;
var _numeric_;
run;

proc print data=table noobs; run;

                      B                                J                 W
                       a                 L     A        e     P  R  R  T  i
                    A  r  C     J  J     o     l  H  J  f     h  o  o  h  l
                    l  b  a  J  a  o  J  u  M  f  e  a  f  J  i  b  n  o  l
        S     r     i  a  r  a  n  y  u  i  a  r  n  m  r  o  l  e  a  m  i
        e     o     c  r  o  n  e  c  d  s  r  e  r  e  e  h  i  r  l  a  a
        x     w     e  a  l  e  t  e  y  e  y  d  y  s  y  n  p  t  d  s  m

        F  Alice    0  0  0  1  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0
        F  Barbara  0  0  0  1  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0
        F  Carol    1  1  0  1  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0
        F  Jane     0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0
        F  Janet    1  1  1  1  0  1  1  1  0  0  0  0  0  0  0  0  0  0  0
        F  Joyce    0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
        F  Judy     1  1  0  1  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0
        F  Louise   0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0
        F  Mary     1  1  1  1  0  1  1  1  0  0  0  0  0  0  0  0  0  0  0
        M  Alfred   0  0  0  0  0  0  0  0  0  0  0  1  1  1  0  1  0  1  0
        M  Henry    0  0  0  0  0  0  0  0  0  0  0  1  1  1  0  1  0  1  0
        M  James    0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0
        M  Jeffrey  0  0  0  0  0  0  0  0  0  0  0  1  0  1  0  1  0  1  0
        M  John     0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0
        M  Philip   0  0  0  0  0  0  0  0  0  1  1  1  1  1  0  1  1  1  1
        M  Robert   0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0
        M  Ronald   0  0  0  0  0  0  0  0  0  1  1  1  1  1  0  1  0  1  0
        M  Thomas   0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
        M  William  0  0  0  0  0  0  0  0  0  1  1  1  1  1  0  1  0  1  0

[UdayGuntupalli]

@PGStats, 
     Thanks a lot for responding to my question, I would like to refine my question a little more to offer some clarity on my request. I would like to provide an example of what I would like to be able to achieve and would appreciate your advice in this direction. 

[PGStats]

SQL is not the right tool for that kind of matrix manipulation. I don't see how you would do that without SAS/IML.

[Reeza]

SAS data sets don't really map well to the concept of a matrix. Don't try and think in that manner in SAS, unless you have IML. You can usually do things like this in other manner, if you explain your use case. If you're trying to design a dummy matrix for example, there are specific PROCs. 

---

@UdayGuntupalli wrote:

All, 
      How can I create a matrix/table of zero's and ones. I understand that matrices require package IML and I don't have that. So I would like to do it using Proc SQL if possible. Kindly advise. 

Best 

Uday 

---

[Reeza · Accepted solution]

As a purely academic exercise, here's one way to do this, using a data step. I've set it to use macro variables so you can convert it to a macro later on, if desired and used frequently. I suspect this isn't going to work long term because how SAS stores data is different than other common data analysis tools. You can set the number of columns, rows and the probability of getting a one at the top and then you get a data set called matrix with the 'matrix' as the output. I've attached the code and results below. 

Results:

[PaigeMiller]

All of this is great innovative programming. However I can't possibly see a use for a matrix of zeros and ones like this in SAS outside IML. Once you create it, what are you going to do with it?