I have a data set in the below format(1) and need to make it like mentioned below(2)

[VRD]

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)

[RW9]

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;

[VRD]

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.

[RW9]

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.

[Loko]

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;

[data_null__]

data b;
   infile cards firstobs=2 dsd;
   input (btext btext1 btext2 d np)($);
   obs + 1;
   drop d;
   cards;
btext,btext1,btext2,d,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)
;;;;
   run;
proc print;
   run;
data bb;
   set b(firstobs=1 obs=5);
   output;
   set b(firstobs=6 obs=10 drop=btext);
   btext=btext1;
   output;
   set b(firstobs=11 obs=max drop=btext);
   btext=btext2;
   output;
   drop btext1-btext2;
   run;
proc print;
   run;

[VRD]

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?

[CTorres]

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

[Haikuo]

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

[data_null__]

I believe so.