creating multiple records based on if condition is true

[smackerz]

Hello

So this is probably pretty straightforward but trying to figure out the most efficient way. So this is what the spec  says I need to create for the variable FALOC

"create 1 record per metastasis location, i.e., one record for each CHADRG, CHBNE, CHBRN, CHLVR, CHLNG, CHLYND, CHMES, CHSKN, CHOTH =1.

If all of CHADRG, CHBNE, CHBRN, CHLVR, CHLNG, CHLYND, CHMES, CHSKN, CHOTH are 0, output 1 record for FATESTCD=CURRSTAT (FALOC will be missing)."

And this is the structure filtered to the relevant columns

So my question is what is the most efficient way to do this? Is it using PROC TRANSPOSE , a DO Loop or something else?. I need to change the values of 1 for each testcode (CHADRG, CHBNE, CHBRN etc) to a text value so is it better to do this after or during. Any help would be greatly appreciated

[Reeza]

Transpose via do loop.

[average_joe · Accepted solution]

Here's something to get you started. I assume that FALOC will contain the text of the test code (CHADRG, CHBNE, etc.) if the value = 1. If all of the test codes are 0 then FALOC will be missing. I wasn't clear about the FATESTCD, so I didn't address it.

* mock up some data;
data have;
length subjid 8;
array locs(*) CHADRG CHBNE CHBRN CHLVR CHLNG CHLYND CHMES CHSKN CHOTH;
do subjid = 1 to 10;
   do j = 1 to dim(locs);
      if ranuni(123) < .1 then locs(j) = 1;
      else locs(j) = 0;
   end;
   output;
end;
drop j;
run;


data want;
set have;
length faloc $6;
array locs(*) CHADRG CHBNE CHBRN CHLVR CHLNG CHLYND CHMES CHSKN CHOTH;

do i = 1 to dim(locs);
   if locs(i) = 1 then do;
      faloc = vname(locs(i));
      output;
   end;
end;

if sum(of locs(*)) = 0 then do;
   faloc = '';
   output;
end;

drop i CHADRG CHBNE CHBRN CHLVR CHLNG CHLYND CHMES CHSKN CHOTH;
run;

[Reeza]

@average_joe small suggestion, maybe test if the all are 0 first, that way you can skip the do loop if it's all 0 to be more efficient? 

Changing the do loop to an ELSE would help?

data want;
set have;
length faloc $6;
array locs(*) CHADRG CHBNE CHBRN CHLVR CHLNG CHLYND CHMES CHSKN CHOTH;

if sum(of locs(*)) = 0 then do;
   faloc = '';
   output;
end;
else do i = 1 to dim(locs);
   if locs(i) = 1 then do;
      faloc = vname(locs(i));
      output;
   end;
end;



drop i CHADRG CHBNE CHBRN CHLVR CHLNG CHLYND CHMES CHSKN CHOTH;
run;

[maguiremq]

I'm really confused by this line:

If all of CHADRG, CHBNE, CHBRN, CHLVR, CHLNG, CHLYND, CHMES, CHSKN, CHOTH are 0, output 1 record for FATESTCD=CURRSTAT (FALOC will be missing)."

What is FATESTCD, what is CURRSTAT? Maybe I'm just not understanding?

Here's my shot at it.

data have;
infile datalines delimiter = ',';
input subject :$6. chadrg chbne chbrn chlvr chlng chlynd chmes chskn choth 3.;
datalines;
101001,0,0,0,1,1,1,0,0,1
101002,0,1,0,1,1,1,0,0,0
101003,0,0,0,0,0,0,0,0,0
;
run;

data want (keep = subject category faloc all_zero);
	set have;
		all_zero = (sum(of chadrg -- choth) = 0);
	array _v [*] chadrg -- choth;
	do i = 1 to dim(_v);
		category = vname(_v[i]);
		if category = vname(_v[i]) then faloc = _v[i];
		if all_zero = 1 then faloc = .;
		output;
	end;
run;