BookmarkSubscribeRSS Feed
Maisha_Huq
Quartz | Level 8

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!

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Maisha_Huq
Quartz | Level 8

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? 

ballardw
Super User

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.

 

 

Maisha_Huq
Quartz | Level 8

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Invalid link.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1357 views
  • 2 likes
  • 3 in conversation