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!
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
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
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?
...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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.