topic Re: Data manipulation - how to create a third variable in SAS Programming

Data manipulation - how to create a third variable

desireatem — Sun, 11 Jun 2017 03:41:17 GMT

Create a fourth variable from first three:

data have;
informat id $2. a b 8.;
input id $ A B ;
cards;
01 1 1
01 1 1
01 2 0
02 2 0
02 1 0
02 2 1
03 2 1
03 2 1
03 2 1
04 1 0
04 2 0
04 2 0
05 2 0
05 1 1 
05 2 0 
06 1 1 
06 2 1
06 2 1
06 2 1
;
run;

To create
For a particular id, if A=1 firstly and subsequent A=2 for same id with B = 1 , then C=1.
But if for id A=2 firstly and subsquent A=1 for same id with B=1, then C=0. A=1 most preceed A=2 for C=1.

id A B C
01 1 1 0
01 1 1 0
01 2 0 0
02 2 0 1
02 1 0 1
02 2 1 1
03 2 1 0
03 2 1 0
03 2 1 0
04 1 0 0
04 2 0 0
04 2 0 0
05 2 0 0
05 1 1 0
05 2 0 0
06 1 1 1
06 2 1 1
06 2 1 1
06 2 1 1

Re: Data manipulation - how to create a third variable

Reeza — Sun, 11 Jun 2017 03:42:32 GMT

Note: I've moved this thread to the Base Programming as it is not related to SAS Data Integration Studio or SAS Data Flux where are Data Management Products/tools. Additionally, I've modified your post to correct spelling and to include the data as a data step. Please try to do these on your own in the future.

Re: Data manipulation - how to create a third variable

Patrick — Sun, 11 Jun 2017 03:58:40 GMT

@desireatem

Please provide also a SAS datastep or at least a table which shows your desired output. This will help us to better understand the logic you describe so we don't spend time for a solution which doesn't return what you're after.

Re: Data manipulation - how to create a third variable

Jagadishkatam — Sun, 11 Jun 2017 04:26:19 GMT

Please try

data want;
do until(last.id);
length codec$100.;
set have;
by id;
retain codec;
if a=1 then code='a';
else if a=2 then code='b';
if first.id then codec=code;
else codec=compress(catx('',codec,code));
if prxmatch('m/ab/oi',strip(codec)) and b=1 then c=1;
else if prxmatch('m/ba/oi',strip(codec)) and b=1 then c=0;
else c=0;
end;
do until(last.id);
set have;
by id;
output;
end;
drop code codec;
run;

Re: Data manipulation - how to create a third variable

desireatem — Sun, 11 Jun 2017 04:42:11 GMT

Hello Jag,

Thank you. let me verify.

Best

Re: Data manipulation - how to create a third variable

kiranv_ — Sun, 11 Jun 2017 04:57:17 GMT

Really like your solution @Jagadishkatam

Re: Data manipulation - how to create a third variable

desireatem — Sun, 11 Jun 2017 05:00:38 GMT

Thanks, correct

Re: Data manipulation - how to create a third variable

Reeza — Sun, 11 Jun 2017 05:09:18 GMT

@desireatem you marked the wrong solution correct.

Re: Data manipulation - how to create a third variable

desireatem — Thu, 15 Jun 2017 04:23:08 GMT

Thank you!!!!