topic Re: Interleave rows in a dataset with created variables in SAS Programming

Interleave rows in a dataset with created variables

ANKH1 — Thu, 22 Feb 2024 21:23:59 GMT

I have the following dataset

data have;
input  time group var1 count percent;
datalines;
0	1	1	5	50
0	1	3	5	50
3	1	1	2	20
3	1	3	8	80
3	2	1	3	30
3	2	3	7	70
11	2	1	4	40
11	2	3	6	60
;
run;

The variable group has two values 1 and 2. Per each time and group different combination, there are two rows. Var1 is either 1 or 3 and for each there is a count variable and a percent which were calculated. We need to interleave a row per each time and group combination where var1 is equal to 2 and count=0 and percent=0. The output should look like this:

time	group	var1	count	percent
0	1	1	5	50
0	1	2	0	0
0	1	3	5	50
3	1	1	2	20
3	1	2	0	0
3	1	3	8	80
3	2	1	3	30
3	2	2	0	0
3	2	3	7	70
11	2	1	4	40
11	2	2	0	0
11	2	3	6	60

Re: Interleave rows in a dataset with created variables

Patrick — Thu, 22 Feb 2024 21:56:10 GMT

Your sample data and narrative indicates that you've got always two rows per time and group with var1 being either 1 or 3. It that's true then code as simple as below should do.

data have;
  input time group var1 count percent;
  datalines;
0 1 1 5 50
0 1 3 5 50
3 1 1 2 20
3 1 3 8 80
3 2 1 3 30
3 2 3 7 70
11 2 1 4 40
11 2 3 6 60
;
run;

data want;
  set have;
  output;
  if var1=1 then
    do;
      var1=2;
      count=0;
      percent=0;
      output;
    end;
run;

proc print data=want;
run;

Re: Interleave rows in a dataset with created variables

ANKH1 — Thu, 22 Feb 2024 22:40:39 GMT

Thank you for your response. Your solution works with the assumption you mentioned. Sorry I missed this important aspect, I just realized from looking at another dataset, that var1 per combination can be missing either var1=1 or var1=2. But the output needs to have per time group combination, var1=1 var1=2 and var1=3. This is a sample dataset (please notice the change in the last two observations):

data have;
input  time group var1 count percent;
datalines;
0	1	1	5	50
0	1	3	5	50
3	1	1	2	20
3	1	3	8	80
3	2	1	3	30
3	2	3	7	70
11	2	2	4	40
11	2	3	6	60;
run;

The wanted output:

time	group	var1	count	percent
0	1	1	5	50
0	1	2	0	0
0	1	3	5	50
3	1	1	2	20
3	1	2	0	0
3	1	3	8	80
3	2	1	3	30
3	2	2	0	0
3	2	3	7	70
11	2	1	0	0
11	2	2	4	40
11	2	3	6	60

This should run only when either var1=1 or var1=2 are missing per combination.

Re: Interleave rows in a dataset with created variables

Patrick — Thu, 22 Feb 2024 22:53:51 GMT

Below one option that addresses the updated requirements. The source data must be sorted by time, group, var1

data have;
  input time group var1 count percent;
datalines;
0 1 1 5 50
0 1 3 5 50
3 1 1 2 20
3 1 3 8 80
3 2 1 3 30
3 2 3 7 70
11 2 2 4 40
11 2 3 6 60
;
run;

data template;
  set have;
  by time group var1;
  if first.group;
  count=0;
  percent=0;
  do var1=1 to 3;
    output;
  end;
run;

data want;
  merge template have;
  by time group var1;
run;

proc print data=want;
run;

Re: Interleave rows in a dataset with created variables

ANKH1 — Fri, 23 Feb 2024 18:55:53 GMT

Thank you!