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
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.
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;
Your data step with the array needs some attention PARAM can't be array and variable.
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.
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.
Really appreciate your help. Have a good day:smileylaugh:
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)
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
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.
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.