Date:         Sat, 28 Dec 2002 04:04:19 GMT
Reply-To:     AJL Cary <ajlcary@PACBELL.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         AJL Cary <ajlcary@PACBELL.NET>
Organization: Prodigy Internet http://www.prodigy.com
Subject:      Re: recoding sas variables with data in them

It seems that you have two pieces of data in each variable name: The
variable and a time.
You have many variables and many times per variable. You want to take a
dataset with a DV like

A0B, A10B, A20B, C0B, C10B, C20B,  ....
x11,  x12,   x13,   y11,  y12,  y13, ...
x21,  x22,   x23,   y21,  y22,  y23, ...

And turn it into

Time     AB     CB ...
0           x11    y11
10         x12    y12
20         x13    y13
0           x21    y21
10         x22    y22
20         x23    y23...

Puddin' Man has part of the answer, here is the rest...
DATA TEST;

INPUT A0B A10B A20B B0C B10C B20C ;

CARDS;
1 2 3 4 5 6
7 8 9 10 11 12
run;

PROC SQL;

* GET VARIABLE LIST INTO A MACRO VARIABLE TO MAKE LIFE EASIER;

SELECT Name INTO :mvInputVarList SEPARATED BY " "
       FROM DICTIONARY.COLUMNS WHERE MemName='TEST' ORDER BY Name;

* READ THE DATA AND ASSIGN IT TO THE RIGHT OUTPUT COLUMN ;

DATA FIX;

LENGTH REC Time 8 VarName $32 Data 8;

Set TEST;

DROP &mvInputVarList ;

ARRAY aInputVars &mvInputVarList ;

REC=_N_;

* ITERATE THROUGH INPUT VARS TO GET VALUES ;

DO OVER aInputVars ;

Time=INPUT(COMPRESS(UPCASE(VNAME(aInputVars)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ
'),BEST.);
     VarName=COMPRESS(UPCASE(VNAME(aInputVars)),'0123456789');
     Data = aInputVars ;
     OUTPUT;
END;
RUN;

* SORT THE DATA JUST IN CASE ;

PROC SORT DATA=FIX OUT=FIX2;
   BY Rec Time VarName;
RUN;

* TRANSPOSE THE DATA INTO THE FORM DESIRED ;

PROC TRANSPOSE DATA=FIX2 OUT=RESULT (DROP=_NAME_);
ID VarName;
BY Rec Time;
RUN;


Andrew James Llwellyn Cary
Curmudgeon Emeritas
Reden & Anders, Ltd



"William Kossack" <kossackw@njc.org> wrote in message
news:3E0CCB70.769EF894@njc.org...
> I've been given another wonderfully large sas dataset with variables
> like
> ABCD0_xyz, ABCD10_xyz, ABCD20_xyz, ABCD30_xyz, etc etc
>
> the problem is that the number in the middle is an important piece of
> information that I need to break out into the real dataset so I get
> something like this
> ABCD_xyz    time
> 1                    0
> 2.2                 10
> 3333              20
> 787                30
>
> this would be easy except for the large number of variables that have
> been coded this way.
>
> any suggestions?
>