DATA Step, Macro, Functions and more

how to transfer EXCEL data to 2 dimensional data in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

how to transfer EXCEL data to 2 dimensional data in SAS

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?

 

 

 


Accepted Solutions
Solution
Sunday
Super User
Super User
Posts: 8,111

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang

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


All Replies
Super User
Posts: 5,879

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang
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
Occasional Contributor
Posts: 7

Re: how to transfer EXCEL data to 2 dimensional data in SAS

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

for example, I want to sort car or color.

Super User
Posts: 10,244

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang

Import and transpose, then you can sort by whatever criterion you like.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 23,724

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang

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

Occasional Contributor
Posts: 7

Re: how to transfer EXCEL data to 2 dimensional data in SAS

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

Super User
Posts: 23,724

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang

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


 

 

Solution
Sunday
Super User
Super User
Posts: 8,111

Re: how to transfer EXCEL data to 2 dimensional data in SAS

Posted in reply to walterwang

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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