topic Re: Transpose and fill in in SAS Programming

Transpose and fill in

terjeph — Thu, 08 Feb 2024 19:29:53 GMT

Data Have illustrate a case where a patients receive a specific service in specific periods before death - which is at day 0. I would like to transpose this file and make it like Data Want where there is one row per day per ID and a dummy variable taking the value of 1 if the service is received, 0 if not. In total, the period covered in this example is 14 days (incl. day 0).

Data Have:

Obs	ID	Startday	Endday
1	1	-2	0
2	1	-8	-6
3	1	-11	-10
4	2	-5	-3
5	2	-8	-7

Data Want

Obs	ID	Day	Dummy
1	1	0	1
2	1	-1	1
3	1	-2	1
4	1	-3	0
5	1	-4	0
6	1	-5	0
7	1	-6	1
8	1	-7	1
9	1	-8	1
10	1	-9	0
11	1	-10	1
12	1	-11	1
13	1	-12	0
14	1	-13	0
15	2	0	0
16	2	-1	0
17	2	-2	0
18	2	-3	1
19	2	-4	1
20	2	-5	1
21	2	-6	1
22	2	-7	0
23	2	-8	1
24	2	-9	0
25	2	-10	0
26	2	-11	0
27	2	-12	0
28	2	-13	0

Re: Transpose and fill in

data_null__ — Thu, 08 Feb 2024 20:11:04 GMT

data have;
   infile cards expandtabs;
   input obs id start end;
   cards;
1	1	-2	0
2	1	-8	-6
3	1	-11	-10
4	2	-5	-3
5	2	-8	-7
;;;;
   run;
proc print;
   run;

data expand1;
   do until(last.id);
      set have;
      by id;
      array _rng[-13:0];
      do i = start to end;
         _rng[i]=1;
         end;
      end;
   do i = hbound(_rng) to lbound(_rng) by -1;
      day = i;
      dummy = coalesce(_rng[i],0);
      output;
      end;
   keep id day dummy;
   run;

proc print;
   run;

Re: Transpose and fill in

terjeph — Fri, 09 Feb 2024 08:33:07 GMT

Worked perfect!