topic Data Manipulation in SAS Programming

Data Manipulation

jfaruqui — Tue, 22 Jan 2019 21:50:45 GMT

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.

Re: Data Manipulation

novinosrin — Tue, 22 Jan 2019 21:59:45 GMT

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;

Re: Data Manipulation

Astounding — Tue, 22 Jan 2019 22:05:30 GMT

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

Re: Data Manipulation

PGStats — Tue, 22 Jan 2019 23:04:43 GMT

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

Re: Data Manipulation

mkeintz — Wed, 23 Jan 2019 01:20:15 GMT

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.