SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Question about file structure, proc transpose, etc

Reply
Frequent Contributor
Posts: 131

Question about file structure, proc transpose, etc

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!

Super User
Super User
Posts: 7,942

Re: Question about file structure, proc transpose, etc

Posted in reply to Maisha_Huq

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;
Frequent Contributor
Posts: 131

Re: Question about file structure, proc transpose, etc

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? 

Super User
Posts: 11,343

Re: Question about file structure, proc transpose, etc

Posted in reply to Maisha_Huq

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.

 

 

Frequent Contributor
Posts: 131

Re: Question about file structure, proc transpose, etc

[ Edited ]

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

 

Super User
Super User
Posts: 7,942

Re: Question about file structure, proc transpose, etc

Posted in reply to Maisha_Huq

Invalid link.

Ask a Question
Discussion stats
  • 5 replies
  • 441 views
  • 2 likes
  • 3 in conversation