Hello, all
Do any of you know how to make a two-way table to an one-way table?
For example,
year Jan Feb March April May ...
my dataset: 1993 2 3 2 8 7
1994 6 4 3 2 5
What I want:
y93_1 y93_2 y93_3 y93_4 y93_5 .....y94_1 y94_2 y94_3 y94_4 y94_5....
2 3 2 8 7 6 4 3 2 5
Thank you!
Apart from some name formatting, this should do the trick. Not sure if it can be done with a single proc transpose.
data temp;
input year jan feb mar;
cards;
1993 2 3 2
1994 6 4 3
;
run;
proc transpose data=temp out=temp2;
by year;
run;
data temp3;
set temp2;
ym = year||_name_; /* change this to format var names */
keep ym col1;
run;
proc transpose data=temp3 out=temp4 (drop=_name_) prefix=y;
var col1;
id ym;
run;
Apart from some name formatting, this should do the trick. Not sure if it can be done with a single proc transpose.
data temp;
input year jan feb mar;
cards;
1993 2 3 2
1994 6 4 3
;
run;
proc transpose data=temp out=temp2;
by year;
run;
data temp3;
set temp2;
ym = year||_name_; /* change this to format var names */
keep ym col1;
run;
proc transpose data=temp3 out=temp4 (drop=_name_) prefix=y;
var col1;
id ym;
run;
Thank you, BruceBrad!
Actually last night I tried transpose, and all data became long data. And, I transposed again. (I think it is similar as what you said). It is ok and being one row.
However, my question becomes to: how can I substitute variable names (more than 300 variable names, now all named "COL1-COL300") to the values in the first row?
Re variable names: See the ID statement in my code example.
And of course the ever popular what is doing to be done with the output. The starting data set has some issues depending on how it is used and now ..., How many years are you doing this for?
26 years...
As I asked, what will you do with all of that data on one line in a dataset?
That's why I asked, keeping track of 300+ variables and/or writing code to use them is likely to be mind numbing. And then next year you have another 12 variables to deal with and associated additional logic coding.
For many scenarios having the data in a
ID date value
structure makes more sense, especially if the Date is made as a SAS date valued variable. Then you can use procedures and formats to prepare many types of summaries.
Consider if you wanted to see the total/mean/max/min or similar statistic for months across years with that long data set. How would you do it.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.