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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.