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

how to transfer EXCEL data to 2 dimensional data in SAS

walterwang — Fri, 13 Jul 2018 06:11:11 GMT

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?

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

LinusH — Fri, 13 Jul 2018 06:14:49 GMT

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...?

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

walterwang — Fri, 13 Jul 2018 06:18:06 GMT

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

for example, I want to sort car or color.

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

Kurt_Bremser — Fri, 13 Jul 2018 06:36:43 GMT

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

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

Reeza — Fri, 13 Jul 2018 15:02:26 GMT

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

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

walterwang — Fri, 13 Jul 2018 20:56:06 GMT

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

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

Reeza — Sat, 14 Jul 2018 00:41:10 GMT

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

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

Tom — Sat, 14 Jul 2018 00:45:43 GMT

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