Hi all
I have a dataset as shown below. I would like to do a transpose so the variables will come from the column "name" and the values from "val1".
name | val1 | val2 | val3 | val4 |
Income | 819772 | 819772 | 819772 | 819772 |
Interestrate | 383884 | 383884 | 383884 | 383884 |
BANK | 422388 | 422388 | 422388 | 422388 |
Børnefamilieydelse | 13500 | 13500 | 13500 | 13500 |
Pensionsindtægter | 0 | 0 | 0 | 0 |
Housing | 317914 | 303766 | 300673 | 297636 |
Interestrate | 75800 | 75800 | 75800 | 75800 |
Husleje | 52800 | 52800 | 52800 | 52800 |
Interestrate | 4800 | 4800 | 4800 | 4800 |
El | 5600 | 5600 | 5600 | 5600 |
Kabel TV | 10800 | 10800 | 10800 | 10800 |
I'm planning to do a proc tranpose like this and it seems to do the job fine.
proc transpose data=want
out=transpose
name=val1;
id name;
run;
My problem is that duplicates will occur and there is no way of knowing which. I therefore need a smart way to automatically rename these duplicates while (or before) running the Proc Transpose.
What I would like is either a solution where the new name is constructed on the basis of a foremer name (preferable) or a solution where the duplicates are numbered. I imagine it can look like this:
Result (based on group values):
Income Income-Interestrate ... Housing Housing-Interestrate
xxx xxx xxx xxxx
or maybe (based on number):
Income Interestrate1 ... Housing Interestrate2
xxx xxx xxx xxx
Hope someone can help - Thanks
If you look at your original dataset there is an id variable. You could just capture it, when you create your dataset, and use it as a by variable when running proc transpose.
Hi,
in this case, you can count the number of repetitive name and assign the serial number adjacent to the name and then transpose it. I have followed the same procedure.
i hope this helps you.
name | val1 | val2 | val3 | val4 |
Income | 819772 | 819772 | 819772 | 819772 |
Interestrate | 383884 | 383884 | 383884 | 383884 |
BANK | 422388 | 422388 | 422388 | 422388 |
Børnefamilieydelse | 13500 | 13500 | 13500 | 13500 |
Pensionsindtægter | 0 | 0 | 0 | 0 |
Housing | 317914 | 303766 | 300673 | 297636 |
Interestrate | 75800 | 75800 | 75800 | 75800 |
Husleje | 52800 | 52800 | 52800 | 52800 |
Interestrate | 4800 | 4800 | 4800 | 4800 |
El | 5600 | 5600 | 5600 | 5600 |
Kabel TV | 10800 | 10800 | 10800 | 10800 |
dataset have is above;
proc sort data=have;
by name;
run;
data have_;
set have;
retain count;
by name;
if first.name then count=1;
else count+1;
if count > 1 then new_name=cats(name,count);
else new_name=name;
run;
proc transpose data=have_ out=want name=var1;
id new_name;
var val1;
run;
proc print;
run;
Good Luck.
Thanks,
Jagadish
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.