Conditional insert of rows in SAS

Question

I am interested in creating new rows is SAS that are conditional on specific variables.

Suppose these are the names of the columns RecordID, ItemName, ItemCount, ItemX, ItemY, ItemZ.
ItemX, ItemY, and ItemZ are numeric variables that can be equal to or greater than 0.
When ItemX, ItemY, and/or ItemZ holds a value greater than 0, I want a new row to be created where ItemName contains the variable name (ItemX, ItemY, ItemZ) and ItemCount to contain the numeric value.

For example, if RecordNumber=1 has ItemX=5 and ItemY=10, then ItemName=ItemX and ItemCount=5 will appear in a new row. ItemName=ItemY and ItemCount=10 has its own row.

How should I go about this in SAS? Thanks.

DomPazz DomPazz · Accepted Answer · 2014-01-13T03:59:03

Pretty easy:

data want(keep=ItemName ItemCount);
set have;
array items[3] itemx itemy itemz;
format ItemName $32. ItemCount best.;

do i=1 to 3;
   if items[i] > 0 then do;
      ItemName = vname(items[i]);
      ItemCount = items[i];
      output;
   end;
end;
run;

Create an array of the item variables. Loop over that array. Check your condition. Use the vname() function to get the variable name and the output statement to create the output record.

Conditional insert of rows in SAS

2 Answers