I have a data set from excel. that is the sale number of cars. such as
car\color | black | green | yellow |
very big | 5 | 4 | 67 |
big | 43 | 3 | 5 |
small | 34 | 56 | 6 |
How can I import the data in sas?
If you import that into SAS you will probably get a dataset that looks like this:
data have ;
input car_color &:$10. black green yellow;
cards;
very big 5 4 67
big 43 3 5
small 34 56 6
;
So just use PROC TRANSPOSE to turn the "color" columns into observations instead.
proc transpose data=have
out=want (rename=(car_color=car _name_=color col1=sales))
;
by car_color notsorted ;
run;
Obs car color sales 1 very big black 5 2 very big green 4 3 very big yellow 67 4 big black 43 5 big green 3 6 big yellow 5 7 small black 34 8 small green 56 9 small yellow 6
not that easy. I want to keep color and car.
for example, I want to sort car or color.
Import and transpose, then you can sort by whatever criterion you like.
@walterwang wrote:
not that easy. I want to keep color and car.
for example, I want to sort car or color.
That would mean resturcturing your data from wide to long.
Note that you wouldn't be able to sort it in Excel either in this form.
This website is down at the moment, but when it's up again it has a tutorial:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
sorry, I did make my question clearly.
What i want is from
car\color | black | green | yellow |
very big | 5 | 4 | 67 |
big | 43 | 3 | 5 |
small | 34 | 56 | 6 |
I want get :
car color sales
very big black 5.
.....
Best
The answer is in the link above, which works now, which is PROC TRANSPOSE.
Did not that work?
If not, post the code you tried.
@walterwang wrote:
sorry, I did make my question clearly.
What i want is from
car\color black green yellow very big 5 4 67 big 43 3 5 small 34 56 6
I want get :
car color sales
very big black 5.
.....
Best
If you import that into SAS you will probably get a dataset that looks like this:
data have ;
input car_color &:$10. black green yellow;
cards;
very big 5 4 67
big 43 3 5
small 34 56 6
;
So just use PROC TRANSPOSE to turn the "color" columns into observations instead.
proc transpose data=have
out=want (rename=(car_color=car _name_=color col1=sales))
;
by car_color notsorted ;
run;
Obs car color sales 1 very big black 5 2 very big green 4 3 very big yellow 67 4 big black 43 5 big green 3 6 big yellow 5 7 small black 34 8 small green 56 9 small yellow 6
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.