DATA Step, Macro, Functions and more

Data Set Manipulation

Occasional Contributor
Posts: 13

Data Set Manipulation

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!!

Trusted Advisor
Posts: 1,270

Re: Data Set Manipulation



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;

Occasional Contributor
Posts: 13

Re: Data Set Manipulation

thanks!! worked great! very much appreciated!

Super User
Posts: 10,787

Re: Data Set Manipulation

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.);
123	filled	leave
123	acting	present
456	filled	present
789	filled	leave
474	filled	leave
474	acting	present
212	filled	suspended
287	assingment	present
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 _:;
Occasional Contributor
Posts: 13

Re: Data Set Manipulation

Thanks for this Ksharp. Still not working. Merge and Rename working correctly.
Super User
Posts: 13,583

Re: Data Set Manipulation

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.

Ask a Question
Discussion stats
  • 5 replies
  • 4 in conversation