BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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
UdayGuntupalli
Quartz | Level 8

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

PGStats
Opal | Level 21

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
UdayGuntupalli
Quartz | Level 8

@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

PGStats
Opal | Level 21

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
Reeza
Super User

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
Super User

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

PaigeMiller
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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