topic Re: SAS in SAS Programming

SAS

vinod4842 — Sun, 17 Nov 2019 13:11:28 GMT

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;

Re: SAS

PaigeMiller — Sun, 17 Nov 2019 13:24:44 GMT

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-data-AKA-generate/ta-p/258712

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

Re: SAS

vinod4842 — Sun, 17 Nov 2019 14:10:59 GMT

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

Re: SAS

PaigeMiller — Sun, 17 Nov 2019 14:13:53 GMT

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-data-AKA-generate/ta-p/258712

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

Re: SAS

Tom — Sun, 17 Nov 2019 15:40:27 GMT

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;

Re: SAS

vinod4842 — Sun, 17 Nov 2019 15:56:36 GMT

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'

Re: SAS

vinod4842 — Sun, 17 Nov 2019 16:00:29 GMT

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.

Re: SAS

Tom — Sun, 17 Nov 2019 16:00:58 GMT

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.

Re: SAS

Tom — Sun, 17 Nov 2019 16:02:04 GMT

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

Re: SAS

vinod4842 — Sun, 17 Nov 2019 16:09:21 GMT

Ya working

Re: SAS

vinod4842 — Sun, 17 Nov 2019 16:10:27 GMT

Ya Its working thank you for your support

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