Date:         Thu, 29 Sep 2005 04:56:26 -0400
Reply-To:     ben.powell@CLA.CO.UK
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         ben.powell@CLA.CO.UK
Subject:      Re: Getting data to look a certain way
Content-Type: text/plain; charset=ISO-8859-1

This seems to work, using transpose:

data test;
infile cards dlm=" ";
informat sex $3.;
input state sex percent ss ci;
cards;
1 all 100 1 1
1 m 50 0.5 0.5
1 f 50 0.5 0.5
2 all 100 1 1
2 m 50 0.5 0.5
2 f 50 0.5 0.5
3 all 100 1 1
3 m 50 0.5 0.5
3 f 50 0.5 0.5
;
run;

proc sort data = test;by sex;run;

proc transpose data = test out = tt;
by sex;
var percent ss ci;
id state;
run;

data tt;
set tt;
var = sex || "_" || _NAME_;
drop sex _NAME_;
run;

proc transpose data = tt out = test;
var _1 _2 _3;
id var;
run;

data test;
informat state 8.;
set test;
state = input(tranwrd(_NAME_,"_",""),8.);
drop _NAME_;
run;

proc print data = test noobs;run;

/*
              all_                        f___                        m___
    state   percent   all_ss   all_ci   percent   f___ss   f___ci   percent
  m___ss   m___ci

      1       100        1        1        50       0.5      0.5       50
    0.5      0.5
      2       100        1        1        50       0.5      0.5       50
    0.5      0.5
      3       100        1        1        50       0.5      0.5       50
    0.5      0.5
*/



Rgds.

On Wed, 28 Sep 2005 07:41:58 -0700, kat j <axnjxntx@YAHOO.COM> wrote:

>I’m trying to figure out the best way to get my data
>looking a certain way.
>
>Right now, I have:
>
>State   sex     percent         ss      ci
>1       all          #          #       #
>1       m          #            #       #
>1       f          #            #       #
>2       all          #          #       #
>2       m          #            #       #
>2       f          #            #       #
>3       all          #          #       #
>3       m          #            #       #
>3       f          #            #       #
>
>I need to re-arrange my data so that I have column for
>each level of sex’s percent, ci, & ss.
>I want my data to look like the following.  The end
>result is that it needs to be put it into excel,
>whether I use ods html or proc export.  This will be
>done several times for different demographics – so
>there may be more levels than just 3 (here I’ve got
>overall, male, & female)
>
>State   All_pct  All_ss  All_ci    M_pct  M_ss  M_ci
>F_pct  F_ss  F_ci
>1         #              #           #            #
>#         #          #        #         #
>2         #              #           #            #
>#         #          #        #         #
>3         #              #           #            #
>#         #          #        #         #
>
>
>Some thoughts on how to get my data like this include:
>a) Splitting the data into several datasets – one for
>each level of the demographic, then merge them back
>together after renaming the percent, ss, & ci
>variables
>b) doing several transposes
>c) using the following code (which I inherited):
>
>data qt100 (keep=_state eqt eqtss eqtci eqtm eqtmss
>eqtmci eqtf eqtfss
>  eqtfci);
> set qt100 (;
> by _state;
>
> ci=1.96*sepercent;
> lci=percent-ci;
> uci=percent+ci;
> label ci="CI"  lci="Lower CI"  uci="Upper CI";
>
> retain eqt eqtss eqtci eqtm eqtmss eqtmci eqtf eqtfss
>eqtfci 0;
>
> if sex=0 then do;
>  eqt=percent;
>  eqtss=nsum;
>  eqtci=ci;
> end;
> else if sex=1 then do;
>  eqtm=percent;
>  eqtmss=nsum;
>  eqtmci=ci;
> end;
> else if sex=2 then do;
>  eqtf=percent;
>  eqtfss=nsum;
>  eqtfci=ci;
> end;
>
> label eqt="Ever quit overall percent"
>           eqtss="Ever quit overall sample size"
>           eqtci="Ever quit overall CI"
>           eqtm="Ever quit male percent"
>           eqtmss="Ever quit male sample size"
>           eqtmci="Ever quit male CI"
>           eqtf="Ever quit female percent"
>           eqtfss="Ever quit female sample size"
>           eqtfci="Ever quit female CI";
>
> if last._state then output;
>run;
>
>
>Is there a better, easier, more efficient way to do
>this?
>
>Thanks.
>-Kat
>
>
>
>
>
>______________________________________________________
>Yahoo! for Good
>Donate to the Hurricane Katrina relief effort.
>http://store.yahoo.com/redcross-donate3/