topic Re: Proc transpose workaround in SAS Programming

Proc transpose workaround

VinitvictorCorr — Tue, 17 Sep 2019 06:40:50 GMT

I need a work around for Proc Transpose. Can you suggest anything?

Re: Proc transpose workaround

Kurt_Bremser — Tue, 17 Sep 2019 06:44:24 GMT

What is the reason for not using proc transpose?

Re: Proc transpose workaround

PeterClemmensen — Tue, 17 Sep 2019 06:57:13 GMT

Please be more specific than this.

Explain why you want to avoid PROC TRANSPOSE. This makes it easier to understand your problem.

Re: Proc transpose workaround

VinitvictorCorr — Tue, 17 Sep 2019 07:10:16 GMT

just want to explore myoptions 🙂

Re: Proc transpose workaround

VinitvictorCorr — Tue, 17 Sep 2019 07:10:55 GMT

no particular reason..just want one more option in my bag

Re: Proc transpose workaround

KachiM — Tue, 17 Sep 2019 08:26:15 GMT

@VinitvictorCorr

One way is to use Array.

Give a Data Set, give explanation for transposing with the expected output data set. It will be then easier to help you.

Re: Proc transpose workaround

VinitvictorCorr — Tue, 17 Sep 2019 08:55:07 GMT

lets say

data k;
input subject $ height weight bmi;
datalines;
abc 198 87 22.5
pqr 209 98 25
;
run;

Re: Proc transpose workaround

KachiM — Tue, 17 Sep 2019 09:00:12 GMT

@VinitvictorCorr

Your data is Ok. How do you want to re-shape it without using Proc Transpose? Show your Shape.

Re: Proc transpose workaround

Ksharp — Tue, 17 Sep 2019 11:17:52 GMT

@art297 wrote a macro %transpose for that . @Reeza also mentioned it many times .

Re: Proc transpose workaround

Reeza — Tue, 17 Sep 2019 15:12:31 GMT

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

Re: Proc transpose workaround

art297 — Tue, 17 Sep 2019 15:26:47 GMT

Difficult to say without your showing how you want the data transposed. As @Ksharp mentioned, I was part of a team that wrote two macros for transposing and untransposing SAS datasets. You can find the code and info for each at:

https://github.com/gerhard1050/Untranspose-a-Wide-File
https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-a-dataset/ta-p/433620?title=A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

Art, CEO, AnalystFinder.com

Re: Proc transpose workaround

ballardw — Tue, 17 Sep 2019 19:47:33 GMT

@VinitvictorCorr wrote:

I need a work around for Proc Transpose. Can you suggest anything?

Going from long to wide or wide to long?

Really specific examples really do help as when a "simple" proc transpose doesn't work perhaps two (or more transposes) and merging results; or proc sql with reflexive joins; or sometime even using a report procedure such as tabulate to create an output data set and manipulating.

Re: Proc transpose workaround

Kurt_Bremser — Tue, 17 Sep 2019 20:45:45 GMT

@VinitvictorCorr wrote:
lets say

data k;
input subject $ height weight bmi;
datalines;
abc 198 87 22.5
pqr 209 98 25
;
run;

Try this:

data want;
set k;
array vars _numeric_;
length name $32 value 8;
do i = 1 to dim(vars);
  name = vname(vars{i});
  value = vars{i};
  output;
end;
keep subject name value;
run;

Re: Proc transpose workaround

34reqrwe — Wed, 18 Sep 2019 03:11:36 GMT

datastep

Re: Proc transpose workaround

VinitvictorCorr — Wed, 18 Sep 2019 03:38:14 GMT

@KachiM
I need the output like:-
subject vstestcd vsorres

Re: Proc transpose workaround

KachiM — Wed, 18 Sep 2019 06:19:59 GMT

@VinitvictorCorr

You have given a Data Set as:

data k;
input subject $ height weight bmi;
datalines;
abc 198 87 22.5
pqr 209 98 25
;
run;

I don't see the variable names as given now as:

subject vstestcd vsorres

Please clarify your correct specification.

Re: Proc transpose workaround

VinitvictorCorr — Wed, 18 Sep 2019 07:44:49 GMT

I need the output like:-
subject vstestcd vsorres

Re: Proc transpose workaround

VinitvictorCorr — Wed, 18 Sep 2019 07:47:19 GMT

the output will be like

SUBJECT VSTESTCD VSORRES
abc Height 198
abc Weight 87
abc BMI 22.5
pqr height 209
pqr weight 98
pqr BMI 25

Re: Proc transpose workaround

Kurt_Bremser — Wed, 18 Sep 2019 08:21:29 GMT

@VinitvictorCorr wrote:
the output will be like

SUBJECT VSTESTCD VSORRES
abc Height 198
abc Weight 87
abc BMI 22.5
pqr height 209
pqr weight 98
pqr BMI 25

I already gave you code for that, you only need to change variable names.

Re: Proc transpose workaround

KachiM — Wed, 18 Sep 2019 08:50:41 GMT

@VinitvictorCorr

My program is similar to @Kurt_Bremser .

data k;
input SUBJECT $ height weight bmi;
datalines;
abc 198 87 22.5
pqr 209 98 25
;
run;

The following data step uses an array to hold the Labels for names of the variables (Height, Weight, BMI). Another array (V) is used to get the values of the variables. Using Do-Loop they are individually output to the new Data Set (WANT).

data want;
   array names[3] $ ('Height', 'Weight', 'BMI');
   set k;
   array v height -- bmi;
   do j = 1 to dim(v);
      VSTESTCD = names[j];
      VSORRES = v[j];
      output;
   end;
keep SUBJECT VSTESTCD VSORRES;
run;

The output is: