topic data manipulation in SAS Programming

data manipulation

grace999 — Wed, 14 Jun 2017 14:14:45 GMT

I have the following medicine dosage for each patient. I would like to set it missing once there is a 2nd treatment. See the new dosage column.

Patient ID	Medicine date	dosage	# of date	Treatment	Treatment date	New Dosage
1	1/13/2014	132	7			132
1	1/14/2014	132	8			132
1	1/15/2014	132	9			132
1	1/16/2014	132	10			132
1	1/17/2014	132	11			132
1	1/18/2014	132	12			132
1	1/19/2014	132	13			132
1	1/20/2014	132	14			132
1	1/21/2014	132	15			132
1	1/22/2014	132	16			132
1	1/23/2014	132	17			132
1	1/24/2014	132	18			132
1	1/25/2014	132	19			132
1	1/26/2014	132	20			132
1	1/27/2014	132	21	A	27-Jan-14	132
1	1/28/2014	132	22		.	132
1	1/29/2014	132	23		.	132
1	1/30/2014	419	24	A	10-Feb-14
1	1/31/2014	286	25		.
1	2/1/2014	286	26		.
1	2/2/2014	286	27		.
1	2/3/2014	286	28		.
1	2/4/2014	286	29		.
1	2/5/2014	286	30		.
1	2/6/2014	286	31		.
1	2/7/2014	286	32		.
1	2/8/2014	286	33		.
1	2/9/2014	286	34		.
1	2/10/2014	421	35

Re: data manipulation

ballardw — Wed, 14 Jun 2017 14:27:03 GMT

It isn't clear if the data you show is what you have or what you want. Please clearly indicate what your data looks like Before the manipulation and then After the manipulation.

It also helps if you can provide your example data in the form of a datastep so we can test code. When you paste values we have to write a data step to read the code and may make choices that do not match your data. For example I do not know for sure which, if any, of your varaibles are character or numeric. If I make an assumption of type and write code to handle that but your data differs it is very likely the solution I propose will not work.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: data manipulation

Astounding — Wed, 14 Jun 2017 14:35:48 GMT

Assuming your data is sorted as in your example, you could use:

data want;

set have;

by patient_id;

if first.patient_id then n_treatments = 0;

if treatment='A' then n_treatments + 1;

if n_treatments < 2 then new_dosage = dosage;

drop n_treatments;

run;