BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
walterwang
Obsidian | Level 7

I have a data set from excel. that is the sale number of cars. such as

 

car\colorblackgreenyellow
very big5467
big4335
small34566

 

How can I import the data in sas?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

 

View solution in original post

7 REPLIES 7
LinusH
Tourmaline | Level 20
Should be no problem.
What have you tried so far?
I would say importing Excel data is one of the most covered both here at Communities and SAS doc so Google it...?
Data never sleeps
walterwang
Obsidian | Level 7

not that easy. I want to keep color and car.

for example, I want to sort car or color.

Reeza
Super User

@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/

walterwang
Obsidian | Level 7

sorry, I did make my question clearly.

What i want is from

car\colorblackgreenyellow
very big5467
big4335
small34566

 

I want get :

car color sales

very big   black  5.

.....

 

Best

Reeza
Super User

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


 

 

Tom
Super User Tom
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 705 views
  • 0 likes
  • 5 in conversation