BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
terjeph
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

 

 

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;

Capture.PNG

View solution in original post

2 REPLIES 2
data_null__
Jade | Level 19

 

 

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;

Capture.PNG

terjeph
Obsidian | Level 7

Worked perfect!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1025 views
  • 0 likes
  • 2 in conversation