topic Re: Assigning values in SAS Programming

Assigning values

Pritish — Mon, 29 Jul 2013 04:14:44 GMT

I guess this is fairly simple .. but somehow I am not able to figure out.. Can you please help me?

So I have following data:

DATA HAVE

UNIQUE_KEY VALUE

ABC 100

BBB .

BBB 200

CCC .

CCC 100

DDD .

In above case, what would I like to have as output is following:

DATA WANT

UNIQUE_KEY VALUE NEW_VALUE

ABC 100 100

BBB . 200

BBB 200 200

CCC . 100

CCC 100 100

DDD . .

So basically, I want to replace missing value for keys that have a second record with a valid value that they have in second record (BBB & CCC in above case).

Thanks for your help!

Re: Assigning values

Scott_Mitchell — Mon, 29 Jul 2013 04:23:10 GMT

Hi Pritish,

This should do what you are after.

DATA HAVE;

INFILE DATALINES;

INPUT UNIQUE_KEY $ VALUE;

DATALINES;

ABC 100

BBB .

CCC .

DDD .

RUN;

DATA MORE;

INFILE DATALINES;

INPUT UNIQUE_KEY $ VALUE;

DATALINES;

ABC 100

BBB 200

CCC 100

DDD .

RUN;

PROC SORT DATA=HAVE;

BY UNIQUE_KEY;

RUN;

PROC SORT DATA=MORE;

BY UNIQUE_KEY;

RUN;

DATA WANT;

UPDATE HAVE

MORE;

BY UNIQUE_KEY;

RUN;

If the UNIQUE_KEY in MORE appears in HAVE then the VALUE is updated, if not another observation is added to the WANT dataset with the UNIQUE_KEY and VALUE from MORE.

Regards,

Scott

Re: Assigning values

DJChavda — Mon, 29 Jul 2013 06:07:03 GMT

Please try this

DATA HAVE;
INFILE DATALINES;
INPUT UNIQUE_KEY $ VALUE;
DATALINES;
ABC 100
BBB .
BBB 200
CCC .
CCC 300
RUN;

PROC SORT DATA=HAVE;
by UNIQUE_KEY descending VALUE;
run;

DATA WANT;
SET HAVE;
by UNIQUE_KEY descending VALUE;
retain NEW_VALUE;
if first.UNIQUE_KEY then NEW_VALUE=VALUE;
run;

Is it you are looking for

Re: Assigning values

Scott_Mitchell — Mon, 29 Jul 2013 06:16:31 GMT

How is it a unique key if it has duplicates?

I thought this was a copy paste error on the OP's part given the variable name.

Perhaps I mis-understand what the OP is trying to do?

Re: Assigning values

Patrick — Mon, 29 Jul 2013 07:50:12 GMT

...or:

DATA HAVE;
INFILE DATALINES;
INPUT KEY $ VALUE;
DATALINES;
ABC 100
BBB .
BBB 200
CCC .
CCC 300
RUN;

proc sql;
create table want as
select l.key, r.value
from have l left join (select * from have where not missing(value)) r
on l.key=r.key
;
quit;

Re: Assigning values

Tom — Mon, 29 Jul 2013 11:59:39 GMT

Just merge back the records with non-missing values.

data have ;

input key $ value @@;

cards;

ABC 100 BBB . BBB 200 CCC . CCC 100 DDD .

;;;;

data want ;

merge have have(keep=key value rename=(value=new_value) where=(new_value ne .));

by key;

put (_all_) (=);

run;