BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daree
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

data_null__
Jade | Level 19

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;
Daree
Calcite | Level 5

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.

data_null__
Jade | Level 19

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
Daree
Calcite | Level 5

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

Loko
Barite | Level 11


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)

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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