DATA Step, Macro, Functions and more

Data manipulation - how to create a third variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 303
Accepted Solution

Data manipulation - how to create a third variable

[ Edited ]

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


Accepted Solutions
Solution
‎06-11-2017 12:42 AM
Super User
Posts: 19,817

Re: Data manipulation - how to create a third variable

Posted in reply to desireatem

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. 

View solution in original post


All Replies
Solution
‎06-11-2017 12:42 AM
Super User
Posts: 19,817

Re: Data manipulation - how to create a third variable

Posted in reply to desireatem

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. 

Super Contributor
Posts: 303

Re: Data manipulation - how to create a third variable

Thank you!!!!

Respected Advisor
Posts: 4,173

Re: Data manipulation - how to create a third variable

Posted in reply to desireatem

@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.

Trusted Advisor
Posts: 1,137

Re: Data manipulation - how to create a third variable

Posted in reply to desireatem

 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;
 
Thanks,
Jag
Super Contributor
Posts: 303

Re: Data manipulation - how to create a third variable

Posted in reply to Jagadishkatam

Hello Jag,

Thank you. let me verify.

Best

PROC Star
Posts: 325

Re: Data manipulation - how to create a third variable

Posted in reply to Jagadishkatam

Really like your solution @Jagadishkatam

Super Contributor
Posts: 303

Re: Data manipulation - how to create a third variable

Posted in reply to Jagadishkatam
Thanks, correct
Super User
Posts: 19,817

Re: Data manipulation - how to create a third variable

Posted in reply to desireatem

@desireatem you marked the wrong solution correct. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 224 views
  • 6 likes
  • 5 in conversation