topic Re: Need ideas to transpose a dataset using minimum coding effort in SAS Procedures

Need ideas to transpose a dataset using minimum coding effort

munitech4u — Mon, 30 Jun 2014 08:56:51 GMT

I have a dataset that contain data related to a particular company in a row, over the period of time, say 20 years. So it has observed values of say 10 variables over period of 20 years.

So Like a1-a20 variables for 20 years data and then say b1-b20 variables for 20 years and so on. Like this there are rows related to n companies.

I want to transpose in such a way that data related to "a" transpose and keeps under "a" for 20 years.then next variable "b" will have 20 years of data transposed and so on. The company say C1 will get repeated 20 times for 20 years of data.

Suggestions?

Let me know if you have any questions.

Re: Need ideas to transpose a dataset using minimum coding effort

RW9 — Mon, 30 Jun 2014 09:12:27 GMT

Sorry, test data and output would probably clarify somewhat. You want something like:

id a1 a2 a3...a20 b1 b2...b20 c1..

If so then transpose data for the A variables, then the B variables, then the C variables and merge them together.

Re: Need ideas to transpose a dataset using minimum coding effort

munitech4u — Mon, 30 Jun 2014 09:27:58 GMT

Transposing all variables using n datasteps for n variables and then merging is quite some time taking task, which I have on my last option. I was looking for any better method.

Re: Need ideas to transpose a dataset using minimum coding effort

RW9 — Mon, 30 Jun 2014 09:32:11 GMT

Well, the only other thing I can think of is array processing with by groups. So:

data want;

set have;

by id;

array a{20};

array b{20};

...

process result into next available array based on spec for a or b etc.

if last.id then output;

run;

Re: Need ideas to transpose a dataset using minimum coding effort

Reeza — Mon, 30 Jun 2014 14:39:20 GMT

Here's a good reference for that.

Example 3 is your situation.

SAS Learning Module: Reshaping wide to long using a data step

Re: Need ideas to transpose a dataset using minimum coding effort

Scott_Mitchell — Tue, 01 Jul 2014 04:16:51 GMT

I am not entirely sure if the example you have given reflects the actual state of play in regards to your data or whether it is merely a best case scenario. The following will work if each set of variable follows the convention you have displayed in your example, chiefly that there will always be 20 variables for each grouping A1-20 B1-20 and so on.

PROC SQL NOPRINT;

SELECT

DISTINCT SUBSTR(NAME,1,1) INTO : OUTVARS SEPARATED BY " "

FROM DICTIONARY.COLUMNS

WHERE UPCASE(MEMNAME) = "HAVE"

AND NAME NOT IN ("COMPANY")

;

SELECT

DISTINCT NAME INTO : VARNAMES SEPARATED BY " "

FROM DICTIONARY.COLUMNS

WHERE UPCASE(MEMNAME) = "HAVE"

AND NAME NOT IN ("COMPANY")

ORDER BY NAME

;

QUIT;

%LET DIM1 = %SYSFUNC(LENGTH(%SYSFUNC(COMPRESS(&OUTVARS.))));

%LET DIM2 = %EVAL(%SYSFUNC(COUNTW(&VARNAMES.))/&DIM1.);

DATA WANT;

RETAIN COMPANY PERIOD;

LENGTH PERIOD $2;

SET HAVE;

ARRAY OUTVARS (&DIM1.) &OUTVARS.;

ARRAY INVARS (&DIM1.,&DIM2.) &VARNAMES.;

DO J = 1 TO &DIM2.;

DO I = 1 TO &DIM1.;

OUTVARS{I} = INVARS{I,J};

IF I = 1 THEN PERIOD = COMPRESS(VNAME(INVARS{I,J}),'0123456789','K');

END;

OUTPUT;

END;

KEEP COMPANY PERIOD &OUTVARS.;

RUN;

Re: Need ideas to transpose a dataset using minimum coding effort

Tom — Tue, 01 Jul 2014 05:14:53 GMT

If the variables are all of the same type (numeric vs character) then just use a two dimensional array.

So if you know the variables are a1-a10, b1-b10, c1-c10 then code like this will work.

data want ;

set have ;

array inx(3,10) a1-a10 b1-b10 c1-c10 ;

array outx a b c ;

do year = 1 to 10 ;

do var=1 to 3;

outx(var) = inx(var,year) ;

end;

output;

end;

keep company year a b c ;

run;