topic Re: How to transpose a sas data set using do loop. in SAS Programming

How to transpose a sas data set using do loop.

zqkal — Mon, 26 Aug 2013 16:08:13 GMT

Does anyone know how I could achieve this.

I have a SAS data with 495 variable and I observation. I want to transpose the variable and create 495 observation. I don’t know how to do this in do loop.

Here is an example

SAS data set

Var1 var1_value var2 var2_value var3 var3_value var4 var4_value

subj 2 owner 0 prop 1 doc 1

I want this

Variable value

Subj 2

Owner 0

Prop 1

doc 1

Thanks in advance

Re: How to transpose a sas data set using do loop.

Tom — Mon, 26 Aug 2013 16:56:13 GMT

Use two arrays.

data want ;

set have ;

array name var1 var2 ...... ;

array number var1_value var2_value .... ;

do i =1 to dim(name);

variable = name(i);

value = number(i);

output;

end;

keep variable value ;

run;

IF all of the character variables have the names and all of the numeric variables have the values AND your variables are defined in a logic order you might be able to avoid listing all of the names.

data want ;

set have ;

array name _character_;

array number _numeric_ ;

...

Re: How to transpose a sas data set using do loop.

art297 — Mon, 26 Aug 2013 19:49:07 GMT

Do you really need to do it using a do loop? It would call for a lot less typing if you created and used a macro variable. e.g.:

proc sql noprint;

select "variable="||strip(name)||"; value="||strip(name)||"_value;output;"

into :varnames separated by " "

from dictionary.columns

where libname="WORK" and

memname="HAVE" and

name not contains "_value"

;

quit;

data want ;

set have;

&varnames.;

keep variable value ;

run;

Re: How to transpose a sas data set using do loop.

mkeintz — Tue, 27 Aug 2013 12:59:35 GMT

Art:

I don't know that it is really a lot less typing to apply the do loop, as long as there is a way to avoid typing the list of varible names. One way is the sql way you suggest.

Another is to use Tom's solution, but instead of listing the variables, use the keywords _numeric_ and _character_ as in;

data want (keep=name value);

set have;
array nam {*} $32 _character_;
array val{*} _numeric_ ;

do I=1 to dim(nam);

name=nam{I};

value=val{I};

output;

end;

run;

For folks unfamiliar with the "select ... into" and dictionary.xxx techniques, the array approach is probably more intuitive.

BUT the downside ... while this technique free of requiring a naming converntion for the variable names (i.e. you don't need varnames of xxx and xxx_value) , it absolutely depends on the order of the "name" vars to correspond with the order of the numeric value variables.

regards,

Mark

Re: How to transpose a sas data set using do loop.

zqkal — Tue, 03 Sep 2013 22:16:54 GMT

Thanks everyone

Re: How to transpose a sas data set using do loop.

Haikuo — Tue, 03 Sep 2013 23:29:55 GMT

Just curious, how come your raw data ends up with an odd number of obs(495)? Does it mean your data does not have a complete pairwise match? The following method is based on the assumption that your data strictly follows Variable-Value order and no skipping, it uses DOW, while Mod() will do the same.

proc transpose data=have out=have1;

var _all_;

run;

data want;

do _n_=1 to 2;

set have1;

if _n_=1 then variable=col1;

else value=input(col1,best.);

end;

keep variable value;

run;

Haikuo