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

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

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

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 


Accepted Solutions
Solution
‎05-29-2018 08:26 AM
Super User
Posts: 23,663

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

Posted in reply to UdayGuntupalli

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. 

 

Screen Shot 2018-05-28 at 7.10.28 PM.png

 

Results:

Screen Shot 2018-05-28 at 7.12.12 PM.png

View solution in original post


All Replies
Respected Advisor
Posts: 2,982

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

[ Edited ]
Posted in reply to UdayGuntupalli

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; 

 

--
Paige Miller
Contributor
Posts: 30

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

Posted in reply to PaigeMiller

@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). 

 

image.png

 

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. 

Esteemed Advisor
Posts: 5,521

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

Posted in reply to UdayGuntupalli

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
PG
Contributor
Posts: 30

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

@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. 

 

image.png

Esteemed Advisor
Posts: 5,521

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

Posted in reply to UdayGuntupalli

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

PG
Super User
Posts: 23,663

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

Posted in reply to UdayGuntupalli

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 


 

Solution
‎05-29-2018 08:26 AM
Super User
Posts: 23,663

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

Posted in reply to UdayGuntupalli

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. 

 

Screen Shot 2018-05-28 at 7.10.28 PM.png

 

Results:

Screen Shot 2018-05-28 at 7.12.12 PM.png

Respected Advisor
Posts: 2,982

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

[ Edited ]

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? 

--
Paige Miller
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 298 views
  • 6 likes
  • 4 in conversation