topic Re: How do I create a data set from a list of row and column positions? in SAS Programming

How do I create a data set from a list of row and column positions?

fpb1 — Mon, 10 Jan 2022 12:48:58 GMT

I have a list of row and column pairs. I would like to create a new dataset such that each row/column pair from the list is assigned a value of 1, otherwise a value of zero.

data start;
input r c;
datalines;
1 1
2 3
3 4
;
run;

I would like to end up with

1 0 0 0

0 0 1 0

0 0 0 1

0 0 0 0

I cannot use PROC IML because my actual dataset is too large and I do not have enough memory.

Re: How do I create a data set from a list of row and column positions?

PaigeMiller — Mon, 10 Jan 2022 13:06:04 GMT

Can you back up and give us a broader picture of what this problem is? There are other ways of creating columns of zeros and ones that don't require PROC IML, if only we knew what you are doing we might be able to recommend some other way.

Re: How do I create a data set from a list of row and column positions?

Ksharp — Mon, 10 Jan 2022 13:09:01 GMT

It is IML things. Hope you have SAS/IML .

data start;
input r c;
v=1;
datalines;
1 1
2 3
3 4
;
run;


proc iml;
use start;
read all var{v r c} into x;
close;
want=full(x);
create want from want;
append from want;
close;
quit;


proc print noobs;run;

Re: How do I create a data set from a list of row and column positions?

Ksharp — Mon, 10 Jan 2022 13:15:50 GMT

If you really want data step , try this one :

data start;
input r c;
datalines;
1 1
2 3
3 4
;
run;


%let nrow=4;
%let ncol=4;
data want;
 if _n_=1 then do;
  if 0 then set start;
  declare hash h(dataset:'start');
  h.definekey('r','c');
  h.definedone();
 end;

array x{&ncol.};

do r=1 to &nrow.;
do c=1 to &ncol.;
 if h.check()=0 then x{c}=1;
  else x{c}=0;
end;
output;
end;

keep x:;
run;

Re: How do I create a data set from a list of row and column positions?

yabwon — Mon, 10 Jan 2022 13:24:35 GMT

Try this:

data start;
input r c;
datalines;
1 1
2 3
3 4
;
run;

proc sql noprint;
select max(max(r,c)) into :size
from start;
quit;

proc sort data = start;
  by r c;
run;

data want;
  set start end=eof;
  by r c;

  array x[&size.];

  if first.r then 
    do _N_ = 1 to &size.;
      x[_N_]=0;  
    end;

  x[c] = 1;

  if last.r then 
    do;
      output;
      r_count + 1;
    end;
  keep x:;
  if eof and r_count NE &size. then
    do;
      do _N_ = 1 to &size.;
        x[_N_]=0;  
      end;

      do _N_ = r_count+1 to &size.;
        output;
      end;
    end;
run;
proc print;
run;

Bart

Re: How do I create a data set from a list of row and column positions?

fpb1 — Mon, 10 Jan 2022 13:56:04 GMT

This worked perfectly, thank you. The IML solution as well, but as I mentioned, my data are too large for it.