BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
a2veeram
Calcite | Level 5

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.

1 ACCEPTED SOLUTION
9 REPLIES 9
a2veeram
Calcite | Level 5

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

I will go ahead and try your suggestions.

THANKS!

a2veeram
Calcite | Level 5

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
Super User


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
Calcite | Level 5

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
Super User

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

Reeza
Super User

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
Calcite | Level 5

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
Ammonite | Level 13

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

id food;

by respondent;

var consumed;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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