topic Re: How to ungroup a dataset? in New SAS User

How to ungroup a dataset?

pikola11 — Fri, 06 Jan 2023 08:49:25 GMT

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!

Re: How to ungroup a dataset?

PeterClemmensen — Fri, 06 Jan 2023 09:40:11 GMT

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?

Re: How to ungroup a dataset?

FreelanceReinh — Fri, 06 Jan 2023 10:28:47 GMT

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

Re: How to ungroup a dataset?

FreelanceReinh — Fri, 06 Jan 2023 10:46:52 GMT

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.

Re: How to ungroup a dataset?

ballardw — Fri, 06 Jan 2023 17:15:06 GMT

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.

Re: How to ungroup a dataset?

Tom — Fri, 06 Jan 2023 17:13:29 GMT

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;

Re: How to ungroup a dataset?

pikola11 — Sat, 07 Jan 2023 14:35:43 GMT

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