BookmarkSubscribeRSS Feed
Pritish
Quartz | Level 8

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!


5 REPLIES 5
Scott_Mitchell
Quartz | Level 8

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
Obsidian | Level 7

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
Quartz | Level 8

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
Opal | Level 21

...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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1146 views
  • 1 like
  • 5 in conversation