DATA Step, Macro, Functions and more

Data table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Data table

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.


Accepted Solutions
Solution
‎01-06-2015 11:54 AM
Super User
Posts: 17,912

All Replies
Solution
‎01-06-2015 11:54 AM
Super User
Posts: 17,912

Re: Data table

Occasional Contributor
Posts: 13

Re: Data table

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

I will go ahead and try your suggestions.

THANKS!

Occasional Contributor
Posts: 13

Re: Data table

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!

Super User
Posts: 17,912

Re: Data table


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?



Occasional Contributor
Posts: 13

Re: Data table

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

Super User
Posts: 17,912

Re: Data table

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

Super User
Posts: 17,912

Re: Data table

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.

Occasional Contributor
Posts: 13

Re: Data table

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 ;

Trusted Advisor
Posts: 1,204

Re: Data table

proc transpose data=have out=want(drop=_Smiley Happy;

id food;

by respondent;

var consumed;

run;

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 445 views
  • 1 like
  • 3 in conversation