Data table

[a2veeram]

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.

[Reeza · Accepted solution]

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

[a2veeram]

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

I will go ahead and try your suggestions.

THANKS!

[a2veeram]

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!

[Reeza]

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?

[a2veeram]

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

[Reeza]

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

[Reeza]

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.

[a2veeram]

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 ;

[stat_sas]

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

id food;

by respondent;

var consumed;

run;