Data Set Manipulation

[Geeman]

Hey SASers!

I am trying to create a simple derive to a variable but not sure how to create it. Maybe using a when or select when statement...

I want SAS to check when a value Filled and Acting to change the pay from Leave to Present such that when variable name APtype is filled and acting for the same person id then their pay should change to present. Note the person can be in the dataset twice. See attached example:

Thank you!!

[stat_sas]

Hi,

Please try the below sql code:

proc sql;
create table want as
select id,aptype, case when count(distinct aptype)>1 then "present" else pay end as pay format=$15. from have
group by id;
quit;

[Geeman]

thanks!! worked great! very much appreciated!

[Ksharp]

You'd better post data in this forum , not attaching a file.Nobody would like to download the file.

data have;
infile cards expandtabs truncover;
input id	(aptype	pay) (:$20.);
cards;
123	filled	leave
123	acting	present
456	filled	present
789	filled	leave
474	filled	leave
474	acting	present
212	filled	suspended
287	assingment	present
;
run;
data want;
 merge have have(keep=id pay rename=(id=_id pay=_pay) firstobs=2);
 if id=_id and pay='leave' and _pay='present' then pay='present';
drop _:;
run;

[Geeman]

Thanks for this Ksharp. Still not working. Merge and Rename working correctly.

[ballardw]

For "not working" please provide how is is not working: no output, wrong output, unexpected output. Provide example of the input, the expeceted or desired output and the actual output, and helps to be very explicit in referencing values. Also it may help to post the log of the code run in case of typos in the code or other syntax issues.