DATA Step, Macro, Functions and more

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

Reply
Contributor VRD
Contributor
Posts: 33

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

btextbtext1btext2N (%)
dsdfXXX (XX)
sdfgXXX (XX)
sdsaXXX (XX)
asferXXX (XX)
dfdfaXXX (XX)
dsdfdfgXXX (XX)
sdfgssaXXX (XX)
sdsaggdXXX (XX)
asferwerXXX (XX)
dfdfadnvXXX (XX)
dsdfxvfsdbXXX (XX)
sdfgvsadvaXXX (XX)
sdsasvasdvXXX (XX)
asferAfgsXXX (XX)
dfdfasdvfasXXX (XX)

I need to make it  like below -2

N (%)
dsdfXXX (XX)
dfgXXX (XX)
xvfsdbXXX (XX)
sdfgXXX (XX)
ssaXXX (XX)
vsadvaXXX (XX)
sdsaXXX (XX)
ggdXXX (XX)
svasdvXXX (XX)
Super User
Super User
Posts: 7,950

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

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;

Contributor VRD
Contributor
Posts: 33

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

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.

Super User
Super User
Posts: 7,950

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

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.

Super Contributor
Posts: 308

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

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;

Respected Advisor
Posts: 3,799

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

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;
Contributor VRD
Contributor
Posts: 33

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

Posted in reply to data_null__
btext1btext2btext3trt1trt2trt3
sample1XXXXXXXXX
sample1ana1XXXXXXXXX
sample1ana1ban1XXXXXXXXX
sample1ana1ban2XXXXXXXXX
sample1ana1ban3XXXXXXXXX
sample1ana1ban4XXXXXXXXX

texttrt1trt2trt3
sample1XXXXXXXXX
ana1XXXXXXXXX
ban1XXXXXXXXX
ban2XXXXXXXXX
ban3XXXXXXXXX
ban4XXXXXXXXX

can any one help me in this?

Regular Contributor
Posts: 180

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

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=bSmiley Happy;
  length text $ 8;
  set have;
  text=coalescec(btext3,btext2,btext1);
run;

CTorres

Respected Advisor
Posts: 3,156

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

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

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

I believe so.

Ask a Question
Discussion stats
  • 9 replies
  • 653 views
  • 1 like
  • 6 in conversation