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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.