Data Manipulation

[jfaruqui]

I want to manipulate my existing dataset as below:

PartNum	AltPartNum	Price		PartNum	Price
998138	.	113		998138	113
809011	805682	20	➡️	809011	20
998204	.	165		805682	20
				998204	165

essentially, if there is an AltPartNum in an observation, I want to add the AltPartNum as PartNum in a new observation with the original price and basically drop the AltPartNum column altogether. want to do it in one data step or proc sql.

Thanks in advance.

[novinosrin · Accepted solution]

This assumes there are only2 vars to check

  data have;
  input PartNum	AltPartNum	Price;
  cards;
998138	.	113	 	998138	113
809011	805682	20	➡️	809011	20
998204	.	165
;

data want;
set have;
if n(PartNum,AltPartNum)=2 then do;
output;
PartNum=AltPartNum;
output;
end;
else if PartNum>. then output;
drop AltPartNum;
run;

[Astounding]

It should be simple:

data want (drop=AltPartNum);

set have;

output;

if AltPartNum > .;

PartNum = AltPartNum;

output;

run;

The sample code assumes that the variables are numeric.  If they're character, the IF statement would read:

if AltPartNum > ' ';

[PGStats]

You could use the missing() function to cover both numeric and character cases in a unified way.

[mkeintz]

I would suggest the coalescec function, which returns the left most non-missing (non-blank in the case of character variables) argument from a list of arguments:

data want (drop=altpartnum);
  set have;
  partnum=coalesce(altpartnum,partnum);
run;

If PartNum and AltPartnum are numeric variables, then use the coalesce function instead.