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

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

VRD — Fri, 29 Aug 2014 11:43:48 GMT

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)

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

RW9 — Fri, 29 Aug 2014 12:05:34 GMT

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;

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

Loko — Fri, 29 Aug 2014 12:14:03 GMT

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;

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

VRD — Fri, 29 Aug 2014 12:17:11 GMT

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.

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

RW9 — Fri, 29 Aug 2014 12:27:04 GMT

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.

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

data_null__ — Fri, 29 Aug 2014 12:28:44 GMT

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; 

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

VRD — Fri, 29 Aug 2014 13:16:52 GMT

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?

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

Haikuo — Fri, 29 Aug 2014 13:47:48 GMT

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

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

CTorres — Fri, 29 Aug 2014 13:49:24 GMT

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

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

data_null__ — Fri, 29 Aug 2014 14:39:59 GMT

I believe so.