Assigning values

[Pritish]

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!

[Scott_Mitchell]

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

[DJChavda]

Hi

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

[Scott_Mitchell]

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?

[Patrick]

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

[Tom]

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;