Reorder data

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Reorder data


Hello SAS community,

How can I reorder my data assigned to variables by following a pattern?

Let's say this is the data:

data start;

   input ID $2. var1 $2. var2 $2. var3 $2. var4 $2.;

   datalines;

A P K L M

B K L M P

C M P K Z

D L K P V

;

run;

I would like var1 to have P only if it is a data point and '.' if not and var2 to have K, var3 to have Z and var4 to have M...

IDVar1Var2Var3Var4
APK.M
BPK.M
CPKZM
DPK..

The real data set has the same problem to a larger magnitude, a lot of extraneaous data that's not useful.

Any suggestions to tackle this one?

Thanks


Accepted Solutions
Solution
‎08-06-2014 04:07 PM
Respected Advisor
Posts: 3,786

Re: Reorder data

This might be heading a direction that is somewhat general.

data targets;
   input t $ @@;
   cards;
PI KP Z ME L
;;;;
   run;
proc transpose data=targets out=tarray(drop=_Smiley Happy prefix=t;
   var t;
   run;
data _null_;
  
call symputx('t0',nobs);
   stop;
  
set targets(drop=_all_) nobs=nobs;
   run;

data start;
   input (ID var1-var4)(:$2.);
   array x[&t0] $2;
  
if _n_ eq 1 then set tarray;
   array t
  • t:;
  •    do i = 1 to dim(x);
          x = ifc(whichC(t,of varSmiley Happy,t,' ');
          end;
      
    drop i var: t:;
       datalines;
    A PI KP L ME
    B KP L ME PI
    C ME PI KP Z
    D L KP PI V
    ;;;;
       run;
    proc print;
      
    run;

    View solution in original post


    All Replies
    Respected Advisor
    Posts: 3,786

    Re: Reorder data

    This works for your example data but I doubt it would work for your real data.  How do you decide that var1 should be P var2 K etc?

    data start;
       input (ID var1-var4)(:$1.);
       array x[4] $1;
      
    array t[4] $1 _temporary_ ('P' 'K' 'Z' 'M');
       do i = 1 to dim(x);
          x = compress(cats(of varSmiley Happy,t,'K');
          end;
      
    drop i var:;
       rename x1-x4=var1-var4;
       datalines;
    A P K L M
    B K L M P
    C M P K Z
    D L K P V
    ;
    run;
    proc print;
      
    run;
    Respected Advisor
    Posts: 4,811

    Re: Reorder data

    Try this:

    data want;

    set start;

    if whichc("P", of var1-var4)>0 then v1 = "P";

    if whichc("K", of var1-var4)>0 then v2 = "K";

    if whichc("Z", of var1-var4)>0 then v3 = "Z";

    if whichc("M", of var1-var4)>0 then v4 = "M";

    keep id v1-v4;

    run;


    proc print data=want noobs; run;

    PG

    PG
    Contributor
    Posts: 29

    Re: Reorder data

    @data_null_;—It's definitely not dynamically known, which var corresponds to which. I had a similar approach, but yours is better. With real data, how would I pass the columns into the compress?

    @PGStats—This is great. I didn't even know this approach...thanks!

    Respected Advisor
    Posts: 3,786

    Re: Reorder data

    jbear wrote:

    @data_null_;—It's definitely not dynamically known, which var corresponds to which. I had a similar approach, but yours is better. With real data, how would I pass the columns into the compress?

    You pass the real data columns to COMPRESS the same way I did using a "SAS Variable" list.

    How real is this example data?  Do you always single character values?  If you have words using WHICHC would continue to work where COMPRESS won't.

    Contributor
    Posts: 29

    Re: Reorder data

    @data_null;—You are correct the compress wouldn't work on the real data, but the whichc would. I had this as the real data:

    IDvar1var2var3var4var5var6var7var8var9var10var11
    000000000000000123TREAYZWVITYLLJKABBNNMQQWLWAZXCBNN

    and I'm interested in only 7 values (see temp array) so it ends like this:

    IDfinalVar1finalVar2finalVar3finalVar4finalVar5finalVar6finalVar7
    000000000000000123AYZWVI.....

    I was working on this to try to get the finalVar results...

    data want (drop = i);
          set work.temp3;
          array valVar[11] $3. _TEMPORARY_ ('AYZ' 'WVI' 'TMQ' 'PLT' 'TRE' 'VQQ' 'ERT');
          array var[11] $3.;
          array finalVar[11] $3.;
                do i = 1 to dim(finalVar);
                      finalVar = whichc(var, of valVar);
                end;
    run;

    Solution
    ‎08-06-2014 04:07 PM
    Respected Advisor
    Posts: 3,786

    Re: Reorder data

    This might be heading a direction that is somewhat general.

    data targets;
       input t $ @@;
       cards;
    PI KP Z ME L
    ;;;;
       run;
    proc transpose data=targets out=tarray(drop=_Smiley Happy prefix=t;
       var t;
       run;
    data _null_;
      
    call symputx('t0',nobs);
       stop;
      
    set targets(drop=_all_) nobs=nobs;
       run;

    data start;
       input (ID var1-var4)(:$2.);
       array x[&t0] $2;
      
    if _n_ eq 1 then set tarray;
       array t
  • t:;
  •    do i = 1 to dim(x);
          x = ifc(whichC(t,of varSmiley Happy,t,' ');
          end;
      
    drop i var: t:;
       datalines;
    A PI KP L ME
    B KP L ME PI
    C ME PI KP Z
    D L KP PI V
    ;;;;
       run;
    proc print;
      
    run;
    Contributor
    Posts: 29

    Re: Reorder data

    @data_null;—This is awesome. That was spot on. Thank you very much.

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 7 replies
    • 280 views
    • 3 likes
    • 3 in conversation