How to duplicate rows for a parameter by subject

[JennieWalker]

Hi all

I am seeking advice/suggestions on how to duplicate rows for a parameter if a subject did not do a scheduled test. I have a long dataset with multiple records per subject reporting the results of an ECG (heart rate, QT interval, QRS duration…). Some subjects did not do the ECG test at the scheduled time and have only a single row with the reason why the test was not done (shown in bold). The current dataset looks like the one below.

SUBJID	VISIT	PARAM	VAL	RSN
1	Screening	Heart rate	X1
1	Screening	QT interval	Y1
1	Screening	QRS duration	Z1
1	Day 49	Heart rate	X2
1	Day 49	QT interval	Y2
1	Day 49	QRS duration	Z2
1	Day 35	ECG	Not done	Not done

I would like to duplicate Day 35, where the test was not done (shown in bold), for each of the parameters. The desired dataset is shown below.

SUBJID	VISIT	PARAM	VAL	RSN
1	Screening	Heart rate	X1
1	Screening	QT interval	Y1
1	Screening	QRS duration	Z1
1	Day 49	Heart rate	X2
1	Day 49	QT interval	Y2
1	Day 49	QRS duration	Z2
1	Day 35	Heart rate	Not done	Not done
1	Day 35	QT interval	Not done	Not done
1	Day 35	QRS duration	Not done	Not done

I did this a very long way but I feel there is something better. The steps I took to do this are below.

1. DS1. Added a column “Join” = “ECG” for the join in DS4.
2. DS2. Created a dataset from DS1 keeping only records ‘where VAL= “Not Done” '
3. DS3. Created a dataset from DS1 of unique parameters (‘select distinct PARAM’) - in this example there are 3
4. DS4. ‘Inner join’ DS2 and DS3 on the ‘Join’ variable 
5. DS5. Kept records from DS1 ‘where VAL ne “Not done”’
6. DS6. Append DS4 and DS5

Any suggestions/tips/advice for a better approach are welcome!

Thanks in advance for your time

Jennie

[ballardw · Accepted solution]

IF your starting data does not have more than one observation when you have that Param=ECG and Not done values then something like this should suffice:

data want;
   set have;
   if Param='ECG' and Val='Not done' then do;
      Param='Heart rate'; output;
      Param='QT interval'; output;
      Param='QRS duration'; output;
   end;
  else output;
run;

The OUTPUT statement tells SAS to write to the output destination when encountered. If change the value of one variable, such as Param, all the others stay the same and are written to the output such as the Subjid, Visit, Value and Rsn

Since you want to write 3 values then 3 outputs.

However, if you have other stuff for day 35, because you didn't show the next observations, then this may not be quite correct.