How to ungroup a dataset?

[pikola11]

Hi SAS experts!

I would like to ungroup my dataset so that instead of columns "Number" and "Other" I would get a column named "Case"  where I would put "1" for each case in column "Number" and "0" for each case in column "Other".

My table looks like this:

Ght	Method	Number	Other
0	0	1	1
1	0	2	0
0	1	0	1
1	1	1	2

I would like to get this:

Ght	Method	Case
0	0	1
1	0	1
1	0	1
1	1	1
0	0	0
0	1	0
1	1	0
1	1	0

Thank you in advance for any suggestions!

[PeterClemmensen]

I don't understand this. Please be more specific about the logic. For example, why is there only 1 obs with Ght = 0 when both Number and Other are equal to 1?

[FreelanceReinh]

Hi pikola11 and welcome to the SAS Support Communities!

data have;
input Ght Method Number Other;
cards;
0 0 1 1
1 0 2 0
0 1 0 1
1 1 1 2
;

data want;
do until(lr1);
  set have end=lr1;
  case=1;
  do _n_=1 to number;
    output;
  end;
end;
do until(lr2);
  set have end=lr2;
  case=0;
  do _n_=1 to other;
    output;
  end;
end;
drop number other;
run;

[This is my fifth attempt to post at least the most important parts of my original reply. The first four were removed by some filter for no good reason.]

[FreelanceReinh]

Hi @pikola11 and welcome to the SAS Support Communities!

Apologies for posting the above as a screenshot. I am just desperately trying to get this reply online.

[ballardw]

Please post example data as a data step. If you can't do that at least paste it as text. Your particular "table" doesn't like getting pasted as text. Do not expect us to retype bad formatted text if any longer than this.

I think:

data have;
   input ght method number other;
datalines;
0 0 1 1
1 0 2 0
0 1 0 1
1 1 1 2
;

data want;
   set have;
   do i=1 to number;
      case=1;
      output;
   end;
   do i=1 to other;
      case=0;
      output;
   end;
   keep ght method case;
run;

However your output is apparently considerably reordered than that of the original set and you did not mention that or explain how the order is to be derived. The rows are in this output but a different order.

The DO loops will expect only positive values of 1 or greater for the Number and Other variables so do not execute when 0 is encountered. If you have negative values then you need to describe what should happen. Each loop will execute the "number of cases" times.

The OUTPUT statement says to write to the  output data set only when encountered. So only when there is a "case" encountered.

The KEEP indicates which variables are in the output data set.

[Tom · Accepted solution]

Just use an OUTPUT statement in an iterative DO loop.

data want;
  set have;
  Case=1;
  do i=1 to number; output; end;
  Case=0;
  do i=1 to other; output; end;
  drop i number other ;
run;

If you want it re-sorted so the cases appear first then add a PROC SORT.

proc sort data=want; 
  by descending case;
run;

[pikola11]

Thank you so much! That's exactly what I wanted! I appreciate your time!