topic Re: creating multiple records based on if condition is true in SAS Programming

creating multiple records based on if condition is true

smackerz — Mon, 25 Apr 2022 14:30:44 GMT

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

Re: creating multiple records based on if condition is true

Reeza — Mon, 25 Apr 2022 16:14:56 GMT

Transpose via do loop.

Re: creating multiple records based on if condition is true

average_joe — Mon, 25 Apr 2022 16:51:46 GMT

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;

Re: creating multiple records based on if condition is true

maguiremq — Mon, 25 Apr 2022 16:52:22 GMT

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;

Re: creating multiple records based on if condition is true

Reeza — Mon, 25 Apr 2022 19:35:27 GMT

@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;