topic How can I convert the value to be a part of a column with EG 5.1(change the Table structure) in SAS Enterprise Guide

How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

Daree — Wed, 10 Dec 2014 10:01:39 GMT

Hello All

I got stuck with a request, need to convert the value to be a part of a column, I knew there is a functionality named 'Transpose' which allows user to convert a value into a column.

But this time I might need to merge one particular value with some columns in the table. let me give u the example.

Here is the original table

ID	Status	Parameter1	Parameter2	Parameter3
10023	AFH2	2.1	50.1	30
10023	AFH1	3.3	78.2	77

And here is the target table I wanted

ID	Parameter1_AFH2	Parameter2_AFH2	Parameter3_AFH2	Parameter1_AFH1	Parameter2_AFH1	Parameter3_AFH1
10023	2.1	50.1	30	3.3	78.2	77

As you saw, the purse is to exclude the duplicated rows. Does anyone know how to convert the structure like this ?

Thanks in advant

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

RW9 — Wed, 10 Dec 2014 10:23:23 GMT

Hi,

Well, my first question would be why do you want to have variables like that. As your data expands you could end up with hundreds of them.

Anyways, the way I would go about it is to first normalize your data:

data inter (keep=id status param res);

set have;

array parameter{3}; /* Note you could find this number 3 from max from sashelp.vcolumn */

do I=1 to3;

param=I;

res=param{I};

output;

end;

run;

You now have a table which looks like:

id status param res;

10023 AFH2 1 2.1

10023 AFH2 2 50.1

10023 AFH2 3 30

...

Me, I would stop at that point as you can then do most functions grouping by id, status, param. However, you can take it one level higher I suppose by:

data inter;

set inter;

length tran_val $50;

tran_val="Parameter_"||strip(put(param,best.))||"_"||strip(status);

run;

proc transpose data=inter out=want;

var res;

by id;

id tran_val;

idlabel tran_val;

run;

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

data_null__ — Wed, 10 Dec 2014 12:22:37 GMT

Your data step with the array needs some attention PARAM can't be array and variable.

data status;
   infile cards expandtabs; 
   input (ID  Status)($)  Parameter1  Parameter2  Parameter3;
   cards; 
10023 AFH2  2.1   50.1  30
10023 AFH1  3.3   78.2  77
;;;;
   run; 
data inter (keep=id status param res);
   set status;
   array parameter{*} parameter:;     /* You don't need the three at all if you use a SAS Variable List*/
   do param=1 to dim(parameter);
      res=parameter{param};
      output; 
      end; 
   run;

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

data_null__ — Wed, 10 Dec 2014 12:30:45 GMT

This is fairly easy with two transposes and the new features of the ID statement make creating the new variables easier than it used to be.

data status;
   infile cards expandtabs; 
   input (ID  Status)($)  Parameter1  Parameter2  Parameter3;
   cards; 
10023 AFH2  2.1   50.1  30
10023 AFH1  3.3   78.2  77
;;;;
   run; 

proc transpose out=step1;
   by id status notsorted; 
   var parameter:;
   run; 
proc print; 
   run; 
proc transpose out=step2(drop=_name_) delim=_;
   by id;
   var col1;
   id _name_ status;
   run; 
proc print; 
   run;

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

Daree — Wed, 10 Dec 2014 13:09:49 GMT

Thanks for the reply,RW9. I was trying to make a chart break down the data by several status which requires the id should be uniqe in the table. Thanks again for the help.

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

Loko — Wed, 10 Dec 2014 13:11:27 GMT

Hello,

%macro trans (base, vars);

%let i=1;

%do %until(%scan(&vars, &i) = );

proc transpose data=&base out=want&i (drop=_name_) prefix=Parameter&i;
by id;
id status;
var Parameter&i;
run;

%let i=%eval(&i+1);

%end;

data want;
merge want1-want%eval(&i-1);
by id;
run;

%mend trans;

%trans(have, Parameter1 Parameter2 Parameter3)

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

Daree — Wed, 10 Dec 2014 13:13:10 GMT

Really appreciate your help. Have a good day:smileylaugh:

Re: How can I convert the value to be a part of a column with EG 5.1(change the Table structure)

Ksharp — Sun, 14 Dec 2014 07:54:43 GMT

But NULL's code is not efficient when you have a big table , and you also need to re-order the variables.

data status;
   infile cards expandtabs; 
   input (ID  Status)($)  Parameter1  Parameter2  Parameter3;
   cards; 
10023 AFH2  2.1   50.1  30
10023 AFH1  3.3   78.2  77
;;;;
   run; 

proc sql;
 select distinct 'status(where=(id="'||strip(id)||'" and status="'||strip(status)||'"  )
                       rename=(Parameter1=Parameter1_'||strip(status)||' 
                               Parameter2=Parameter2_'||strip(status)||'
                               Parameter3=Parameter3_'||strip(status)||'))' into : list separated by ' ' from status;
quit;
%put &list ;

data want;
 merge &list ;
 by id;
 drop Status;
run;

Xia Keshan