DATA Step, Macro, Functions and more

reading data from a multi-way table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

reading data from a multi-way table

Hi there,

 

I'm trying to read data from a table that has multiple 'dimensions'. At first, it is just a 3 way table like this:

 

                        smoker    nonsmoker

male     white       10           20

            others      20           30

female  white       30           40

             others      30           30

 

and I just write:

input gender race smoker count;

cards;

1 1 1 10

1 1 2 20

...

however when the table becomes bigger and bigger and having more variables the task becomes quite hard (like the table in the attachment), and I'm wondering if there are any easier way to input data like that?


1.jpg

Accepted Solutions
Solution
‎11-09-2016 03:35 AM
Super User
Posts: 9,671

Re: reading data from a multi-way table

You could read it as and transpose it.



data have;
input    var1 $ var2 $       smoker    nonsmoker;
cards;
male    white       10           20
 .      others      20           30
female  white       30           40
 .      others      30           30
;
run;
proc transpose data=have out=want;
by var1 var2 notsorted;
var smoker nonsmoker;
run;

View solution in original post


All Replies
Solution
‎11-09-2016 03:35 AM
Super User
Posts: 9,671

Re: reading data from a multi-way table

You could read it as and transpose it.



data have;
input    var1 $ var2 $       smoker    nonsmoker;
cards;
male    white       10           20
 .      others      20           30
female  white       30           40
 .      others      30           30
;
run;
proc transpose data=have out=want;
by var1 var2 notsorted;
var smoker nonsmoker;
run;

Occasional Contributor
Posts: 14

Re: reading data from a multi-way table

[ Edited ]

Thanks so much ksharp!

Is there any way I can do to rename the variable '_name_' in the transposed dataset which has the value of 'smoker' to a numerical variable 1, nonsmoker to 2?

also, how can I renane the variable col1 in the transposed variable?

Super User
Posts: 9,671

Re: reading data from a multi-way table


options validvarname=any;
data have;
input    var1 $ var2 $    '1'n '2'n;
cards;
male    white       10           20
 .      others      20           30
female  white       30           40
 .      others      30           30
;
run;
proc transpose data=have out=want(rename=(col1=value));
by var1 var2 notsorted;
var '1'n '2'n ;
run;

Super User
Posts: 9,671

Re: reading data from a multi-way table



options validvarname=any;
data have;
input    var1 $ var2 $    '1'n '2'n;
cards;
male    white       10           20
 .      others      20           30
female  white       30           40
 .      others      30           30
;
run;
proc transpose data=have name=smoke out=want(rename=(col1=value));
by var1 var2 notsorted;
var '1'n '2'n ;
run;

Occasional Contributor
Posts: 14

Re: reading data from a multi-way table

THANKS again!!!
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 286 views
  • 3 likes
  • 2 in conversation