Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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

IDStatusParameter1Parameter2Parameter3
10023AFH22.150.130
10023AFH13.378.277

And here is the target table I wanted

IDParameter1_AFH2Parameter2_AFH2Parameter3_AFH2Parameter1_AFH1Parameter2_AFH1Parameter3_AFH1
100232.150.1303.378.277

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

Thanks in advant


Accepted Solutions
Solution
‎12-10-2014 07:30 AM
Respected Advisor
Posts: 3,775

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

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;


12-10-2014 6-29-07 AM.png

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,223

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

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;

Respected Advisor
Posts: 3,775

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

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;
New Contributor
Posts: 3

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

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.

Solution
‎12-10-2014 07:30 AM
Respected Advisor
Posts: 3,775

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

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;


12-10-2014 6-29-07 AM.png
New Contributor
Posts: 3

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

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

Super Contributor
Posts: 305

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


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)

Grand Advisor
Posts: 9,582

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 448 views
  • 0 likes
  • 5 in conversation