topic Re: Data table in SAS Programming

Data table

a2veeram — Tue, 06 Jan 2015 16:50:04 GMT

Hello,

I am fairly new to SAS community. I am trying to prepare my data for further analysis. In this preparation I need to rearrange the table..

I have a table with a few entries for each respondent. I would like to see 1 respondent in each row..

Each respondent consumed certain foods and I need these foods to become columns so that I can just see how much of each they consumed.

My current table look something like:

Resp1 Dairy products 5g

Resp1 Wheat products 15g

Resp1 Meat 333g

Resp1 Soy products 21g

Resp1 Fish 52g

Resp2 Dairy products 17g

Resp2 Meat 21g

Resp2 Green vegetables 64g

Resp2 Citrus fruit 27g

Resp2 Fish 34g

I would like it to be like this:

Resp \ Food in gr Dairy Wheat Meat Soy Fish Green veg Citrus

Resp1 5 15 333 21 52 0 0

Resp2 17 0 21 0 34 64 27

Is it at all possible in SAS..?

I would greatly appreciate any suggestions, links, ideas..!

Thank you very much in advance.

Re: Data table

Reeza — Tue, 06 Jan 2015 16:54:19 GMT

Keyword is TRANSPOSE

Two different ways are explained here:

SAS Learning Module: How to reshape data long to wide using proc transpose

SAS Learning Module: Reshaping data long to wide using the data step

Re: Data table

a2veeram — Tue, 06 Jan 2015 16:59:22 GMT

Thank you so much, Reeza, for such a prompt response!

I will go ahead and try your suggestions.

THANKS!

Re: Data table

stat_sas — Tue, 06 Jan 2015 17:02:50 GMT

proc transpose data=have out=want(drop=_:);

id food;

by respondent;

var consumed;

run;

Re: Data table

a2veeram — Tue, 06 Jan 2015 21:32:13 GMT

I ran into some issues.. Syntax error.. I think I am making a mistake with the variables that contain an underscore.. But I could not get it to work..

I used:

PROC SORT DATA=long OUT=longsorted ;
BY id ;
RUN ;

DATA transposed;
SET longsorted ;
BY id ;

KEEP id FD_consumed1-FD_consumed10;
RETAIN FD_consumed1-FD_consumed10;

ARRAY aFD_consumed(1:10) FD_consumed1-FD_consumed10;

IF first.id THEN
DO;
DO i = 1 to 10;
aFD_consumed( i ) = 0 ;
END;
END;

aFD_consumed( FD_code ) = FD_consumed ;

IF last.id THEN OUTPUT ;

Do you have any suggestions how to fix this..?

Thank you so much for your time!

Re: Data table

Reeza — Tue, 06 Jan 2015 21:34:41 GMT

Is your year 1 to 10? If its not you can't reference the array that way.

aFD_consumed( year ) = aFD_consumed ;

Do you actually have a year variable?

Re: Data table

a2veeram — Tue, 06 Jan 2015 21:42:02 GMT

Oh, I am sorry - it is FD_code (food code) variable in the brackets.. Which is also with the underscore.. Food code describes foods 1 - Animal products 2 - vegetables 3 - fruit and so on... until 10. FD_consumed10 describes amounts consumed.

(I inserted this command here as an example of what I used in SAS, but in SAS I checked all the variables, they are correct.. )

Re: Data table

Reeza — Tue, 06 Jan 2015 22:45:23 GMT

You have to post your actual code/log/errors. Its a guessing game otherwise.

Re: Data table

Reeza — Tue, 06 Jan 2015 22:46:11 GMT

Or you can try Stat@SAS code as well, proc transpose should be straightforward. I generally only use a data step if I need to do multiple variables at once.

Re: Data table

a2veeram — Wed, 07 Jan 2015 13:23:26 GMT

Thanks! I'll try the proc transpose.

The problem is that I am working from a research centre where the data is confidential, and it does not have any Internet access. So I can't give the actual code/log/errors. I take the syntax with me there and try it and then go out to get int. access to look for troubleshooting and other codes. I'll do my best to try a few things.. But so far the log underlines the name of the variable aFD_consumed and tells me that there is a syntax error in the line aFD_consumed( i ) = 0 ;