topic Re: Data Set Manipulation in SAS Programming

Data Set Manipulation

Geeman — Tue, 12 Jul 2016 01:24:40 GMT

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

Re: Data Set Manipulation

stat_sas — Tue, 12 Jul 2016 03:28:24 GMT

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;

Re: Data Set Manipulation

Ksharp — Tue, 12 Jul 2016 03:29:09 GMT

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;

Re: Data Set Manipulation

Geeman — Tue, 12 Jul 2016 14:14:06 GMT

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

Re: Data Set Manipulation

ballardw — Tue, 12 Jul 2016 15:15:11 GMT

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.

Re: Data Set Manipulation

Geeman — Wed, 13 Jul 2016 00:54:09 GMT

thanks!! worked great! very much appreciated!