topic Re: Proc Transpose Alternative in SAS Programming

Proc Transpose Alternative

bnarang — Tue, 04 Dec 2012 18:41:12 GMT

Hi Everyone

While Transposing any vertical format dataset with proc transpose, I believe we cannot give multiple ID variables. But my problem is similar. With the By group I would like to have data with multiple ID variable to check my data. What I understood, I need to write multiple transpose procedures and then merge back. However, I am sure it might not be very efficient way to work on such problem. Please shed light on this if you know anything alternative that might be useful.

Thanks

Re: Proc Transpose Alternative

Linlin — Tue, 04 Dec 2012 18:48:34 GMT

Hi,

You could either use array or proc summary.

Re: Proc Transpose Alternative

Tom — Tue, 04 Dec 2012 18:55:14 GMT

You can use multiple variables in the ID statement. Here is an simple example.

data tall;

do year=2011 to 2012; do qtr=1 to 4;

input count @@ ; output;

end; end;

cards;

1 2 3 4 5 6 7 8

run;

proc transpose data=tall out=wide prefix=Y delim=Q;

id year qtr;

var count;

run;

proc print data=wide; run;

Perhaps you want to transpose multiple VAR statements into columns rather than rows?

There is trick using the IDGROUP feature of PROC SUMMARY that DATA_NULL_ has posted here and on SAS-L.

Re: Proc Transpose Alternative

Ksharp — Wed, 05 Dec 2012 03:30:24 GMT

One choice is proc means + idgroup , check it at document.

But I will use data step to catch it which is very fast.

Ksharp

Re: Proc Transpose Alternative

bnarang — Wed, 05 Dec 2012 07:58:29 GMT

Hi All,

I looked for Proc means + idgroup statement as suggested by all, but I got error: I refered to this document on the web to do the same:

http://www.sascommunity.org/wiki/Transpose_using_IDGROUP_in_PROC_SUMMARY

"The parameter value 139 is not within the required range of 1 and 100" .

Please help on this,

Re: Proc Transpose Alternative

data_null__ — Wed, 05 Dec 2012 10:49:16 GMT

139 is not going to work. The value in the OUT operand of the IDGROUP option must be between 1 and 100.

Can you explain your requirement in more detail? Also, post some sample data using the have/need model.

Re: Proc Transpose Alternative

bnarang — Wed, 05 Dec 2012 17:34:01 GMT

Hi Data_null, So basically, I have a dataset like this:

ID Month Var1 Var2

1 200101 SD DE

1 200102 EF GH

1 200103 HK LM

2 200101 GH LM

2 200105 JH KJ

Now Here I would like to change the format of the current dataset in the following way:

ID Var1_200101 Var1_200102 Var1_200103 Var1_200104 Var1_200105 Var2_200101 Var2_200102 Var2_200103 Var2_200104 Var2_200105

1 SD EF HK . . DE GH LM . .

2 GH . . JH LM KJ

This can be achieved by writing multiple Transpose procedures and then Merge back with ID. 2nd way as mentioned above by Proc Means. It worked well but in cases when IDGROUP option is not within 1 - 100 , this will not work which is my case unfortunately.

Thanks

Re: Proc Transpose Alternative

Reeza — Wed, 05 Dec 2012 17:56:58 GMT

I'm assuming your column identifiers will follow a date pattern, ie 201001-201012 201101-201112 and so forth.

Do you know the time period in advance or does that change? Is it set for each run?

Re: Proc Transpose Alternative

Linlin — Wed, 05 Dec 2012 18:35:16 GMT

I would first split the dataset into two, then use DN's proc summary method, then merge the final two datasets.

Re: Proc Transpose Alternative

data_null__ — Wed, 05 Dec 2012 19:14:32 GMT

Given what you have shown with have and need I would do this. It address the naming of the new variables along with ordering. Your transpose variables are all the same type so that is not an issue. This will work well until your data gets large when it will be slow.

data have;
   input ID $  Month:yymmn. Var1 $  Var2 $;
   format month yymmn.; 
   cards; 
1     200101     SD      DE
1     200102     EF       GH
1     200103     HK       LM
2     200101     GH       LM
2     200105     JH       KJ
;;;;
   run; 
proc summary data=have nway; 
   output out=month(drop=_:) min(month)=min max(month)=max; 
   run; 
proc transpose data=have(obs=0) out=vars;
   var var:;
   run; 
data frame(keep=_name_ month);
   set vars;
   if _n_ eq 1 then set month;
   do m = 0 to intck('MONTH',min,max);
      month = intnx('MONTH',min,m);
      output; 
      end; 
   format month yymmn.; 
   run; 

proc transpose data=have out=tall;
   by id month;
   var var:;
   run; 
data tallV / view=tallV;
   set frame tall;
   run; 
proc transpose data=tallV out=wide(where=(not missing(ID))) delim=_;
   by id;
   var col1;
   id _name_ month;
   run;

Re: Proc Transpose Alternative

art297 — Wed, 05 Dec 2012 23:15:50 GMT

DN: As always I'm impressed with your solution. The only thing I would suggest adding is a drop option to omit the _name_ variable in the final data step. i.e.:

proc transpose data=tallV out=wide(drop= _: where=(not missing(ID))) delim=_;

by id;

var col1;

id _name_ month;

run;

Re: Proc Transpose Alternative

Ksharp — Thu, 06 Dec 2012 03:27:25 GMT

I would do this.

data have;
   input ID $  Month:yymmn. Var1 $  Var2 $;
   format month yymmn.;
   cards;
1     200101     SD      DE
1     200102     EF       GH
1     200103     HK       LM
2     200101     GH       LM
2     200105     JH       KJ
;;;;
   run;
proc sql noprint;
 select distinct catt('have(where=(month=',month,') rename=(var1=var1_',put(month,yymmn. -l),' var2=var2_',put(month,yymmn. -l),'))') into : list separated by ' '
  from have;
quit;
data want;
 merge &list ;
 by id;
run;

Ksharp

Re: Proc Transpose Alternative

bnarang — Thu, 06 Dec 2012 07:13:01 GMT

Hi Reeza,

No, the time period is not set. It can be pre-populated dataset

Re: Proc Transpose Alternative

joehinson — Thu, 06 Dec 2012 14:15:09 GMT

A hash-transpose. Not so elegant; way too many hard-coding of variables! Also, dates were handled as character to simplify matters. The hash variables could have been handled with macro variables, through a prior Proc SQL.

data _null_;

if(1=2)then set have;

length var1_200101-var1_200105 var2_200101-var2_200105 $2 ;

declare hash varmon (ordered:"a");

varmon.defineKey("id");

varmon.defineData ("id","Var1_200101","Var1_200102","Var1_200103","Var1_200104","Var1_200105",

"Var2_200101","Var2_200102","Var2_200103","Var2_200104","Var2_200105");

varmon.defineDone();

call missing (of _all_);

array w{5,2} $ _temporary_;

do until(done);

set have end=done;

rcf=varmon.find(key:id);

w(1,1)=Var1_200101;w(2,1)=Var1_200102;w(3,1)=Var1_200103;w(4,1)=Var1_200104;w(5,1)=Var1_200105;

w(1,2)=Var2_200101;w(2,2)=Var2_200102;w(3,2)=Var2_200103;w(4,2)=Var2_200104;w(5,2)=Var2_200105;

mx=whichc(month,"200101","200102","200103","200104","200105");

w(mx,1)=var1;

w(mx,2)=var2;

rcr=varmon.replace(key:id,data:id,data:w(1,1),data:w(2,1),data:w(3,1),data:w(4,1),data:w(5,1),

data:w(1,2),data:w(2,2),data:w(3,2),data:w(4,2),data:w(5,2));

call missing(of _all_);

end;

varmon.output(dataset:"want");

stop;

run;

Re: Proc Transpose Alternative

tc — Thu, 06 Dec 2012 15:30:59 GMT

Perhaps you can cobble something out of this....

It's a Bird, It's a Plane, It's SQL Transpose!
http://www2.sas.com/proceedings/forum2008/089-2008.pdf
Ever wish you could easily transpose and summarize monthly data using just PROC SQL? Regardless of whether the data is stored in a SAS dataset or other relational databases? Even if scores of variables for dozen of months are involved? This paper tackles the task of data denormalization with a simple macro that uses PROC SQL to take a "vertical" table with multiple rows per subject and transform it into a "horizontal" table containing one row per subject with aggregated monthly values.

Re: Proc Transpose Alternative

Haris — Thu, 06 Dec 2012 18:10:05 GMT

data have;

input ID $ Month:yymmn. Var1 $ Var2 $;

format month yymmn.;

cards;

1 200101 SD DE

1 200102 EF GH

1 200103 HK LM

2 200101 GH LM

2 200105 JH KJ

;

run;

proc transpose data=have out=interim;

by ID Month;

var Var1 Var2;

proc transpose data=interim out=want( drop = _name_ );

by ID;

var Col1;

ID _NAME_ Month;

run;

proc sql; drop table interim; quit;

Re: Proc Transpose Alternative

bnarang — Sun, 09 Dec 2012 06:47:54 GMT

I am very thankful to all of you for answering my questions spontaneously. Such a wonderful community. Thanks again.