topic Re: Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject in SAS Programming

Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject

mariko5797 — Thu, 24 Feb 2022 17:40:38 GMT

I want each study included in my dataset for each phase and each subject whether they have any values that day or not. I was thinking of just merging my HAVE dataset with one that contains all the IDs, phases, and days. However, I would rather not manually input every study day with each ID and phase. Is there a quick way to do this?

data have;
 input id $ phase day val @@;
 cards;
 A	1	6	12.1
 A	1	8	15.3
 A	1	10 	12.0
 A	1	12	13.9
 A	1	14	17.1
 A	2	6	21.4
 A	2	8	34.9
 A	2	10	18.6
 A	2	16	12.4
 B	1	6	14.0
 B	1	8	16.1
 B	1	10 	15.4
 B	1	14	18.3
 B	2	6	21.9
 B	2	8	23.9
 B	2	10	17.3
 B	2	12	14.6
 ;
run;

data want;
 input id $ phase day val @@;
 cards;
 A	1	6	12.1
 A	1	8	15.3
 A	1	10 	12.0
 A	1	12	13.9
 A	1	14	17.1
 A	1	16	.	
 A	2	6	21.4
 A	2	8	34.9
 A	2	10	18.6
 A	2	12	.	
 A	2	14	.	
 A	2	16	12.4
 B	1	6	14.0
 B	1	8	16.1
 B	1	10 	15.4
 B	1	12	.	
 B	1	14	18.3
 B	1	16	.	
 B	2	6	21.9
 B	2	8	23.9
 B	2	10	17.3
 B	2	12	14.6
 B	2	14	.	
 B	2	16	.	
 ;
run;

Re: Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject

PeterClemmensen — Thu, 24 Feb 2022 17:58:30 GMT

How about

data have;
 input id $ phase day val @@;
 cards;
 A	1	6	12.1
 A	1	8	15.3
 A	1	10 	12.0
 A	1	12	13.9
 A	1	14	17.1
 A	2	6	21.4
 A	2	8	34.9
 A	2	10	18.6
 A	2	16	12.4
 B	1	6	14.0
 B	1	8	16.1
 B	1	10 	15.4
 B	1	14	18.3
 B	2	6	21.9
 B	2	8	23.9
 B	2	10	17.3
 B	2	12	14.6
 ;
run;

data want(drop = rc);
   if _N_ = 1 then do;
      dcl hash h(dataset : 'have');
	  h.definekey('id', 'phase', 'day');
	  h.definedata('val');
	  h.definedone();
   end;

   set have;
   by id phase;

   if first.phase then do day = 6 to 16 by 2;
      val = .;
	  rc = h.find();
      output;
   end;
run;

Result:

id  phase  day  val 
A   1      6    12.1 
A   1      8    15.3 
A   1      10   12.0 
A   1      12   13.9 
A   1      14   17.1 
A   1      16   . 
A   2      6    21.4 
A   2      8    34.9 
A   2      10   18.6 
A   2      12   . 
A   2      14   . 
A   2      16   12.4 
B   1      6    14.0 
B   1      8    16.1 
B   1      10   15.4 
B   1      12   . 
B   1      14   18.3 
B   1      16   . 
B   2      6    21.9 
B   2      8    23.9 
B   2      10   17.3 
B   2      12   14.6 
B   2      14   . 
B   2      16   .

Re: Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject

PeterClemmensen — Thu, 24 Feb 2022 18:06:16 GMT

Or shorter :

proc summary data = have completetypes nway;
   class id phase day;
   var val;
   output out = want(drop = _:) sum =;
run;

Re: Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject

Chaupak — Thu, 24 Feb 2022 18:11:46 GMT

I assume that the range of day is all from 6 to 16. You can try this:

data have1;
	set have;
	by id phase;
	output;
	if first.phase and day > 6 then do;
		day = 6;
		val = .;
		output;
	end;

	if last.phase and day < 16 then do;
		day = 16;
		val = .;
		output;
	end;

run;

proc sort data=have1;
	by id phase day;
run;

data want;
	set have1;
	by id phase;
	nextrec = _n_+1;
	output;
	
	if last.phase = 0 then do;
		set have1(rename=(day=nextday)) point=nextrec;
		do day =  day + 2 to nextday - 2 by 2;
			val = .;
			output;
		end;
	end;

	drop nextday;
run;

Re: Add column that repeatedly numbers 6 through 12 by 2 for each phase and subject

Ksharp — Fri, 25 Feb 2022 11:58:34 GMT

data have;
 input id $ phase day val @@;
 cards;
 A 1 6 12.1
 A 1 8 15.3
 A 1 10  12.0
 A 1 12 13.9
 A 1 14 17.1
 A 2 6 21.4
 A 2 8 34.9
 A 2 10 18.6
 A 2 16 12.4
 B 1 6 14.0
 B 1 8 16.1
 B 1 10  15.4
 B 1 14 18.3
 B 2 6 21.9
 B 2 8 23.9
 B 2 10 17.3
 B 2 12 14.6
 ;
run;

proc freq data=have noprint;
table id*phase*day/out=all_level(drop=count percent) sparse list;
run;

data want;
 merge all_level have;
 by id phase day;
run;