Hi I have data like this
var1 | var2 | var3 |
a | 16 | 10 |
a | 6 | 19 |
a | 8 | 9 |
a | 7 | 13 |
b | 14 | 14 |
b | 10 | 11 |
a | 14 | 16 |
b | 16 | 12 |
c | 13 | 12 |
b | 10 | 11 |
c | 7 | 18 |
a | 11 | 7 |
b | 13 | 7 |
c | 14 | 10 |
c | 15 | 13 |
b | 17 | 15 |
c | 14 | 11 |
and want to apply these 3 conditions
1:
var2<10
var3>15
then the value in new final column should be = "a-1" (if var1=a), "1-b"(if var1=b), "c-1"(if var1=c)
2:
var2>10
var3>15
then the value in new final column should be = "a-2" (if var1=a), "2-b"(if var1=b), "c-2"(if var1=c)
3:
var2>10
var3<15
then the value in new final column should be = "a-3" (if var1=a), "3-b"(if var1=b), "c-3"(if var1=c)
I want my output be like this
var1 | var2 | var3 | final |
a | 16 | 10 | a-3 |
a | 6 | 19 | a-1 |
a | 8 | 9 | |
a | 7 | 13 | |
b | 14 | 14 | 3-b |
b | 10 | 11 | |
a | 14 | 16 | a-2 |
b | 16 | 12 | 3-b |
c | 13 | 12 | c-3 |
b | 10 | 11 | |
c | 7 | 18 | c-1 |
a | 11 | 7 | a-3 |
b | 13 | 7 | 3-b |
c | 14 | 10 | c-3 |
c | 15 | 13 | c-3 |
b | 17 | 15 | |
c | 14 | 11 | c-3 |
Please help me in code
@Nidhi_vats wrote:
I also tried with these tree loops but is it possible that we can apply these three conditions in single and compact loop?
Hello @Nidhi_vats,
Is this "compact" enough?
data want;
set have;
length final $3;
_n_=3*(var2>10)*(var3~=15)+(var3>15)*((var2~=10)-2*(var2>10));
if _n_ then final=putc(cats(var1,'-',_n_),ifc(var1='b','$revers','$'),3);
run;
data want;
set have;
if var2 lt 10 and var3 gt 15
then do;
if var1 = 'a' then final = 'a-1';
else if var1 = 'b' then final = 'b-1';
else if var1 = 'c' then final = 'c-1';
end;
... similarly for points 2 and 3 ...
run;
Please help me with a single loop
Here's one way.
data want; set have; length newfinal $ 5; select ; when (var2<10) select ; when (var3>15) select (var1); when ('a','c') newfinal = catx('-',var1,1); when ('b') newfinal = catx('-',1,var1); otherwise; end; otherwise; end; when (var2>10) select ; when(var3>15) select (var1); when ('a','c') newfinal = catx('-',var1,2); when ('b') newfinal = catx('-',2,var1); otherwise; end; when(var3<15) select (var1); when ('a','c') newfinal = catx('-',var1,3); when ('b') newfinal = catx('-',3,var1); otherwise; end; otherwise; end; otherwise; end; run;
SELECT/WHEN/END blocks are a way of simplifying multiple similar If/then/else. without a variable after select you can place an expression in the WHEN statement to specify condition. With a variable then you can provide lists of values.
The otherwise statement in each block is what you would do if none of the specified conditions were true.
CATX is a string function to place the first value between all occurrences of other variables and removes any trailing blanks there might be.
Another using a function that can return up to three values depending on a condition
data want; set have; length newfinal $ 5; select ; when (var2<10) select ; when (var3>15) newfinal= ifc(var1='b','1-b',catx('-',var1,1)); otherwise; end; when (var2>10) select ; when (var3>15) newfinal= ifc(var1='b','2-b',catx('-',var1,2)); when (var3<15) newfinal= ifc(var1='b','3-b',catx('-',var1,3)); otherwise; end; otherwise; end; run;
I also tried with these tree loops but is it possible that we can apply these three conditions in single and compact loop?
@Nidhi_vats wrote:
I also tried with these tree loops but is it possible that we can apply these three conditions in single and compact loop?
Maybe i am blind, but i can't see any loops (except for the implicit data-step-loop) in the suggested solution.
@Nidhi_vats wrote:
I also tried with these tree loops but is it possible that we can apply these three conditions in single and compact loop?
Hello @Nidhi_vats,
Is this "compact" enough?
data want;
set have;
length final $3;
_n_=3*(var2>10)*(var3~=15)+(var3>15)*((var2~=10)-2*(var2>10));
if _n_ then final=putc(cats(var1,'-',_n_),ifc(var1='b','$revers','$'),3);
run;
@FreelanceReinh wrote:
@Nidhi_vats wrote:
I also tried with these tree loops but is it possible that we can apply these three conditions in single and compact loop?
Hello @Nidhi_vats,
Is this "compact" enough?
data want; set have; length final $3; _n_=3*(var2>10)*(var3~=15)+(var3>15)*((var2~=10)-2*(var2>10)); if _n_ then final=putc(cats(var1,'-',_n_),ifc(var1='b','$revers','$'),3); run;
Slick.
Of course I am waiting for the OP to come back with another question involving var1 = d (or ??) and another operation like * ….
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.