topic Re: Question about file structure, proc transpose, etc in SAS Data Management

Question about file structure, proc transpose, etc

Maisha_Huq — Mon, 18 Jan 2016 15:23:59 GMT

Hi there -- I'm wondering if someone would have feedback/suggestions: to report on the percent of, say, schools which fall under some list of conditions, is it necessary to have a school-level file?

I’m asking since I see the file I have is a food-level file, where isn't even one food item per record (instead, each food item can be listed more than 100 times). Additionally, the variable for unique school ID's reports frequencies of up to 300-400. Given the current interim file structure, I’m wondering whether the most desirable thing is to convert the food-level file to a school-level one or if there is an alternative. What approach could one take to report percent of schools which offer one food item (example)?

please let me know if i should clarify or if there s a better space for a question like this

Thank you in advance!

Re: Question about file structure, proc transpose, etc

RW9 — Mon, 18 Jan 2016 15:28:32 GMT

Hi,

Yes, test data and required output does kind of help. If you just want shool level from the data though, just filer it out e.g.

proc sort data=have out=want nodupkey;
  by school;
run;

Or if there are conditions then use datastep:

data want;
  set have; 
  by school;
  if first.school and a=b then output;
run;

Re: Question about file structure, proc transpose, etc

ballardw — Tue, 19 Jan 2016 15:50:56 GMT

Without seeing example data my first instinct is to want to create an analysis set that would look something like this:

ProductCode (or product name), School, indicator. The indicator would be 0 when that that product does not appear at that school and 1 when it does.

Then using any of the report procedures, Proc Report, Tabulate, Means or Summary, Sum of the indicator would give counts and mean would yield percents. With proc tabulate you could use PCTSum or colpctsum statistics to get percentages of schools overall or within products.

Re: Question about file structure, proc transpose, etc

Maisha_Huq — Tue, 02 Feb 2016 13:54:21 GMT

Woulld there be another way aside from

- assigning a macro and then callin a proc sort process for each school/day/meal/nutrient/food combo?

- and aside from sorting the dataset by a list of BY vars then creating a concatenated var for each school/day/meal/food nutrient combo then identifying each unique first combo as the one with the least of each nutrient level?

Thank you in advance

Re: Question about file structure, proc transpose, etc

RW9 — Tue, 26 Jan 2016 15:29:02 GMT

Invalid link.

Re: Question about file structure, proc transpose, etc

Maisha_Huq — Wed, 27 Jan 2016 17:19:39 GMT

Thanks RW9 and ballwardw!

Input data set would look something like:

food item - food-level file where each different food item anew for each school where it's offered and each meal within that school it's offfered. So if a food item, an apple, is offered at 100 schools, for both breakfast and lunch, it'll be listed 200 times in the fole, one row for each school and meal combo where it's offered.

meal- breakfast, lunch

day - weekday

nutrient- the amount of a nutrient in that food item

Fooditem School Meal Day Nutrient1 Nutrient2

Apple School1 B Mon 5 cals 10 cals

Apple School1 M Mon 5 cals 10 cals

Apple School1 B Tues 5 cals 10 cals

Apple School1 M Tues 5 cals 10 cals

Apple School1 B Wed 5 cals 10 cals

Apple School1 M Wed 5 cals 10 cals

Apple School1 B Thurs 5 cals 10 cals

Apple School1 M Thurs 5 cals 10 cals

Apple School2 B Mon 5 cals 10 cals

Apple School2 M Mon 5 cals 10 cals

Apple School2 B Tues 5 cals 10 cals

Apple School2 M Tues 5 cals 10 cals

Apple School2 B Wed 5 cals 10 cals

Apple School2 M Wed 5 cals 10 cals

Apple School2 B Thurs 5 cals 10 cals

Apple School2 M Thurs 5 cals 10 cals

Milk School1 B Mon 5 cals 10 cals

Milk School1 M Mon 5 cals 10 cals

Milk School1 B Tues 5 cals 10 cals

Milk School1 M Tues 5 cals 10 cals

Milk School1 B Wed 5 cals 10 cals

Milk School1 M Wed 5 cals 10 cals

Milk School1 B Thurs 5 cals 10 cals

Milk School1 M Thurs 5 cals 10 cals

Milk School2 B Mon 5 cals 10 cals

Milk School2 M Mon 5 cals 10 cals

Milk School2 B Tues 5 cals 10 cals

Milk School2 M Tues 5 cals 10 cals

Milk School2 B Wed 5 cals 10 cals

Milk School2 M Wed 5 cals 10 cals

Milk School2 B Thurs 5 cals 10 cals

Milk School2 M Thurs 5 cals 10 cals

What's your suggestion for the best way to identify the food item with the least amount of each nutrient at each school-meal-day combination If there are up almost 2000 schools and up to 400 food items at a school throughout the week?