SAS

[vinod4842]

I have a file it is combination of some columns a,b,c,d,e .

but i imported the file and then created a column in sam1 

my output is like should be 

account	a1	b1	c1	d1	e1	reson
125422114	520	90	88	90	22	a1|b1|c1|d1|e1
1259852421	570	90		90	22	a1|b1||d1|e1
9987458	417	57	44	20	45	a1|b1|c1|d1|e1

Here my final required output is reason1 has

values in reson a1 & b1 are  'Old_Var'

 values in reson c1,d1 & e1 are 'New_Var'

if  reson in   a1 or b1 and c1 or d or e1 then it would be 'Old&New Var'

if reson any one in a1 or b1 then it would be 'Old Var' in to new variable.

Please help to create a variable by finding the reson values.

proc import datafile='C:\Users\HP\Desktop\datasamplecol.xls' out=sam dbms=xls
replace;
run;



data sam1;
set sam;
if a1 then a11='a1';
if b1 then b11='b1';
if c1 then c11='c1';
if d1 then d11='d1';
if e1 then e11='e1';
reson=compress(a11 || b11 || c11 || d11 || e11 ) ;

run;





proc sql;
create table sam2  as select *,
(case when reson contains ('a1') or reson contains ('b1') and reson contains ('c1') or reson contains ('d1') or reson contains ('e1')
then 'Old & New var' 
when reson contains ('c') or reson contains ('d') or reson contains ('e') then 'New Var' 
when reson contains ('a') or reson contains ('b')then 'Old Var' 
else ' ' end) as reason
from sam1;
run;

[PaigeMiller]

Hi, @vinod4842 

Many of us will not download Microsoft Office documents, as they can be security threats. Please provide your data using these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Also, please help us by going back and changing the Subject of your post to something meaningful.

[vinod4842]

account	a1	b1	c1	d1	e1
125422114	520	90	88	90	22
1259852421	570	90		90	22
9987458	417	57	44	20	45
99877858	778		5987		45
9987458742	570	90		90	22
9987458	417	57
99877858			5987	987	45
99e77858			43	987	85
9987458742	570	90
99754158
45745787				887
778745		552

Above one is data and my requirement is below like this 

and i am struggling

the create the corec_reason column

here a1,b1 are Old and C1,d1,e1 are New

if combination of any letter in reson column a1 or b1 becomes Old 

if any one values of c1 ,d1 ,e1 in reson becomes New

if any one value in  reson column contains  a1 or b1 and c1,d1,e1 becomes  Old & New 

Please help to create the column below dataset

account	a1	b1	c1	d1	e1	reson	corec_reason
125422114	520	90	88	90	22	a1|b1|c1|d1|e1	Old & New
1259852421	570	90		90	22	a1|b1||d1|e1	Old & New
9987458	417	57	44	20	45	a1|b1|c1|d1|e1	Old & New
99877858	778		5987		45	a1||c1||e1	Old & New
9987458742	570	90		90	22	a1|b1||d1|e1	Old & New
9987458	417	57				a1|b1|||	Old & New
99877858			5987	987	45	||c1|d1|e1	New
99e77858			43	987	85	||c1|d1|e1	Old & New
9987458742	570	90				a1|b1|||	Old & New
99754158						||||	Old & New
45745787				887		|||d1|	New
778745		552				|b1|||	Old

[PaigeMiller]

Again, I request the data be provided in the form requested

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

and I request you change the subject of this thread to a meaningful subject

[Tom]

So you want to test if any of A1,B1,C1 are non-missing and whether either of D1 or E1 is non-missing?

One way is to count the number of non-missing values using the N() function.

The CATX() function will let you build your resulting string from the two independent parts.

data want;
  set have ;
  length corec_reason $9;
  corec_reason = catx(' & '
    ,ifc(n(a1,b1,c1),'Old',' ')
    ,ifc(n(d1,e1),'New',' ')
  );
run;

[vinod4842]

not missing & non missing values for corec_reason
here i need to generate old & new variables in other variable based on reson column
for example
here a1 and b1 are old variables
c1 d1 e1 are new variables
we need to show the old & new variable in another new variable that is corec_reason
if reson any of (a1 or b1 and c1 or d1 or e1) becomes combination of old & new variables
because a1 , b1 belongs to old variable and c1,d1,e1 belongs to new variable

if reson in a1 or 'b1' c1 or d1 or e1 then 'old & New'
if reson in a1 then 'Old'
if b1 and d1 then 'Old & new'

[vinod4842]

Sorry it is a1 & b1 are old values and c1 d1 e1 are new values then 

for eg

reson                        Corec_reason

a1|b1|c1|d1|e1	Old & New
a1|b1||d1|e1	Old & New
a1|b1|c1|d1|e1	Old & New
a1||c1||e1	Old & New
a1|b1||d1|e1	Old & New
a1|b1|||	old
||c1|d1|e1	New
||c1|d1|e1	New
a1|b1|||	New
||||
|||d1|	New
|b1|||	Old

Please help team.

[Tom]

Do you really need that column with the pipe characters? How does it add any value over the original columns?

[vinod4842]

Ya working

[Tom · Accepted solution]

So did you try my code?  Looks like you might need change which variables are considered Old and which are New but it should work.

[vinod4842]

Ya Its working thank you for your support