btext | btext1 | btext2 | N (%) | |
dsdf | XXX (XX) | |||
sdfg | XXX (XX) | |||
sdsa | XXX (XX) | |||
asfer | XXX (XX) | |||
dfdfa | XXX (XX) | |||
dsdf | dfg | XXX (XX) | ||
sdfg | ssa | XXX (XX) | ||
sdsa | ggd | XXX (XX) | ||
asfer | wer | XXX (XX) | ||
dfdfa | dnv | XXX (XX) | ||
dsdf | xvfsdb | XXX (XX) | ||
sdfg | vsadva | XXX (XX) | ||
sdsa | svasdv | XXX (XX) | ||
asfer | Afgs | XXX (XX) | ||
dfdfa | sdvfas | XXX (XX) |
I need to make it like below -2
N (%) | |
dsdf | XXX (XX) |
dfg | XXX (XX) |
xvfsdb | XXX (XX) |
sdfg | XXX (XX) |
ssa | XXX (XX) |
vsadva | XXX (XX) |
sdsa | XXX (XX) |
ggd | XXX (XX) |
svasdv | XXX (XX) |
Not sure I completely follow but from a guess, you want the text to be text1 when the other two are null, and text2 when that is not null, and drop the last five records? If so then:
proc sql;
create table WANT as
select COALESCE(BTEXT1,BTEXT) as TEXT label="N (%)",
N label=""
from HAVE
where BTEXT2 is null;
quit;
Sorry for the confusion.
I did not mean to drop the last five records. I just forgot to paste them. Sorry.
I want to compute such that
btext val
btext1 val
btext2 val
should follow in this order along with values.
Minor update then:
proc sql;
create table WANT as
select COALESCE(BTEXT2,BTEXT1,BTEXT) as TEXT label="N (%)",
N label=""
from HAVE;
quit;
The coalesce function takes the first non-missing result from the left proceeding to the right until it finds a non-missing, hence if BTEXT2 is null and BTEXT1 is not null then BTEXT1 is used etc.
Hello,
I don't know if this is what you want:
data have;
infile datalines dsd dlm=' ';
input btext $ btext1 $ btext2 $ N_ $8.;
datalines;
dsdf XXX (XX)
sdfg XXX (XX)
sdsa XXX (XX)
asfer XXX (XX)
dfdfa XXX (XX)
dsdf dfg XXX (XX)
sdfg ssa XXX (XX)
sdsa ggd XXX (XX)
asfer wer XXX (XX)
dfdfa dnv XXX (XX)
dsdf xvfsdb XXX (XX)
sdfg vsadva XXX (XX)
sdsa svasdv XXX (XX)
asfer Afgs XXX (XX)
dfdfa sdvfas XXX (XX)
;
proc sql;
create table want(where=(text ne ' ')) as
select btext as Text, N_ from have
union
select btext1, N_ from have
union
select btext2, N_ from have
;
quit;
btext1 | btext2 | btext3 | trt1 | trt2 | trt3 |
sample1 | XXX | XXX | XXX | ||
sample1 | ana1 | XXX | XXX | XXX | |
sample1 | ana1 | ban1 | XXX | XXX | XXX |
sample1 | ana1 | ban2 | XXX | XXX | XXX |
sample1 | ana1 | ban3 | XXX | XXX | XXX |
sample1 | ana1 | ban4 | XXX | XXX | XXX |
text | trt1 | trt2 | trt3 |
sample1 | XXX | XXX | XXX |
ana1 | XXX | XXX | XXX |
ban1 | XXX | XXX | XXX |
ban2 | XXX | XXX | XXX |
ban3 | XXX | XXX | XXX |
ban4 | XXX | XXX | XXX |
can any one help me in this?
Try this:
data have;
length btext1 btext2 btext3 trt1 trt2 trt3 $ 8 ;
infile datalines dlm=',' missover dsd;
input btext1 btext2 btext3 trt1 trt2 trt3;
cards;
sample1,,,XXX,XXX,XXX
sample1,ana1,,XXX,XXX,XXX
sample1,ana1,ban1,XXX,XXX,XXX
sample1,ana1,ban2,XXX,XXX,XXX
sample1,ana1,ban3,XXX,XXX,XXX
sample1,ana1,ban4,XXX,XXX,XXX
;
run;
data want(drop=b:);
length text $ 8;
set have;
text=coalescec(btext3,btext2,btext1);
run;
CTorres
Super slick, _null_! That is exactly the direction what I want to see. Will the following to less some hard coding? It seems to me the sequence still holds.
data bb_1;
set b;
output;
set b(where=(not missing(btext1)) drop=btext) end=t1;
btext=btext1;
output;
set b(where=(not missing(btext2)) drop=btext) end=t2;
btext=btext2;
output;
drop btext1-btext2;
if t1 or t2 then
stop;
run;
Regards,
Haikuo
I believe so.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: