Compute and graph the running inventory

Reply
Contributor das
Contributor
Posts: 44

Compute and graph the running inventory

I need help to figure this one out. I don't program enough to be very proficient at this.

I have a database about animals in a herd of sheep and goats. We want to evaluate the daily fluctuation in herd size (total number of sheep, total number of goats, and total number of animals) over a date range input by the user. For evaluation, the output will be graphed in a multi-line plot.

The relevant variables I have to work with include:

Animal_ID (alphanumeric)

Specie (sheep or goat)

Date_Arrived (date format)

Date_Gone (date format)

I've not been able to figure this out in SAS or in Microsoft Access (the native database). I think I need to be able to create an output that contains the total number of animals present each day within a requested date range. I'm assuming this is involves using iterative loops and perhaps an initializing macro to query the user for the date range of interest.

Thank you for your insights and help.

Dave

Super Contributor
Posts: 644

Re: Compute and graph the running inventory

To get you started: the size of the flocks at any given date (assuming all animals have a non null date arrived) you can use this (untested) code:

%Let    init_date   =   '1Jan2010'D ;

%Let    end_date    =   '31Dec2010'D ;

Proc SQL ;

    Create table init_flock as

        Select  &Init_Date As Date Format = Date9.

            ,   Specie

            ,   Animal_Id

            ,   Date_arrived

            ,   Date_gone

        From    All_Animals

        Where   Date_Arrived <= &Init_Date

            And (   Date_Gone >= &Init_Date

                Or  Date_Gone IS NULL

                )

            ;

    Create Table Counts As

        Select  Date

            ,   Specie

            ,   Count (*)  As Flock_Size

        From    Init_Flock

        Group

            By  Date

            ,   Specie

            ;

Quit ;

      

Richard

Super Contributor
Posts: 644

Re: Compute and graph the running inventory

To get the flock history between the given dates you could either set a macro calculate the flock size using my code above and append each daily result to a time series, or try and get code like this to work (no guarantees!)

Data Flock_history ;

    Set     Counts end = done ;

    Retain  Goats

            Sheep

            ;

    Array   Countx {2} Goats Sheep ; 

    Array   Speciex {2} $ _TEMPORARY_ ('Goat ' 'Sheep') ;

    Count {_N_}  = Flock_Size ;

    If      Done

      Then

        Do ;

            Output ;

        Do  Date   = (&init_Date + 1) to &End_Date ;

            Do  Q = 1 to 2 ;

                Set All_Animals (Where =  (Specie = Speciex {Q}}

                                          And Date_Arrived = Count_Date

                                ))

                Count {Q} + 1 ;

                Set All_Animals (Where =  (Specie = Speciex {q}}

                                          And Date_Gone = Count_Date

                                ))

                Count {Q} + (-1) ;

            End ;

            Output ;

        End ;

        End ;

    Keep    Date

            Goats

            Sheep

            ;

Run ;

It would speed things up if you had a compound index in your animal database on Specie and Date_Arrived, and another on Specie and Date_Gone.

If you can't get this to work and want the macro option instead let me know.

I would export the history to Excel and create the charts there as X-Y line charts.

Richard

Contributor das
Contributor
Posts: 44

Re: Compute and graph the running inventory

Posted in reply to RichardinOz

Richard,

I am thrilled with such a quick response. Thank you. I'll try working through your offerings and will likely post some follow up questions. I think your second suggestion looks the most familiar to me after searching out answers elsewhere.

Thanks for getting me rolling.

Dave

Super Contributor
Posts: 644

Re: Compute and graph the running inventory

If by the second option you want a macro solution, here goes (remember i can't test this right now so you may have to hunt down missing semicolons and parentheses...)

%Let    init_date   =   '1Jan2010'D ;

%Let    end_date    =   '31Dec2010'D ;

Macro Count_Flock (atdate) ;

Proc SQL ;

    Create table Counts as

        Select  &atdate As Date Format = Date9.

            ,   Specie

            ,   Count (*) As Count

        From    All_Animals

        Where   Date_Arrived <= &atdate

            And (   Date_Gone >= &atdate

                Or  Date_Gone IS NULL

                )

            ;

Quit ;

     

Data Flock_Counts ;

    Set     Counts end = done ;

    Retain  Goats

            Sheep

            ;

    Array   Countx {2} Goats Sheep ;

    Count {_N_}  = Flock_Size ;

    If done then

        Do ;

            Total = SUM (Goats, Sheep) ;

            Output

        End ;

    Keep Date Goats Sheep Total ;

    Put Date date9. goats= sheep= Total= ;

Run ;

Proc Append

                Base = Flock_History

                Data  = Flock_Counts

                ;

Run ;


%Mend ;

Data Do_Counts ;

     Do Date = &Init_Date to &End_Date ;

           Datechr = Put (Date, Best.) ;

           Call Execute (CATT ('%Count_Flock(', Datechr.,');')) ;

     End ;

Run ;

You could set a daily scheduled job to update the totals:

%Count_Flock(today()) ;


Richard

Contributor das
Contributor
Posts: 44

Re: Compute and graph the running inventory

Posted in reply to RichardinOz

Richard,

I'm not quite getting this (the macro method) to work. Not sure where the problem lies. I added a ';' after the Output statement and removed the '.' after the Datechr within the CATT statementCall. Here is the code as submitted:

Code.PNG

When it runs it loops without end. After stopping it, here is the recurring error message:

ErrorLog.PNG

I think it's close but don't know where the problem lies.

Dave

Super User
Posts: 19,822

Re: Compute and graph the running inventory

You've declared an array called countx but are using count() as a function? I don't know if that's a SAS function you're expecting something out of or if you meant to use your countx array.

Neither does SAS Smiley Happy

Super Contributor
Posts: 644

Re: Compute and graph the running inventory

Sorry - I don't currently have access to SAS to test my code. 

The SQL should have a  clause

     group by 1, 2

after the from clause

The middle step in the macro is really just a transpose and could be replaced by the proc except for the total - it just seemed heavy handed to do that but my solution could hardly pass as elegant.  End = done is a set option and should not have been in brackets.

count {_N_} should have been countx{_N_}.

Here is another way of transposing and totalling the data in one step:

Data Flock_Counts ;

    Merge  Counts  (where  = (specie = 'Goat'

                            rename = (Flock_Size = Goats)

                           )

               Counts  (where  = (specie = 'Sheep'

                            rename = (Flock_Size = Sheep)

                           )

               ;

    Drop Specie ;

    Total = SUM (Goats, Sheep) ;

    Put Date date9. goats= sheep= Total= ;

Run ;

BTW you may need to change the capitalisation of goat and sheep to match your data, or use the propcase function if the data is not consistent

Richard

Super Contributor
Posts: 339

Re: Compute and graph the running inventory


Hi,

I would've approached the problem in a different way. Unless you have millions of heads, you shouldn't see too big a difference in execution time and it is quite easier programmatically;

data all_animals_dailyseries;

     set all_animals;

     length newdate 8.;

     format newdate date9.;

     if date_gone=. then daystotal=today()-date_arrived;

     else daystotal=date_gone-date_arrived;

     do i=0 to daystotal;

           newdate=date_arrived+i;

           ouput;

     end;

     drop daystotal

run;

This creates one record per day which a head has been at the farm. As such, afterwards, you can use simple queries on newdate variable to achieve everything else you wanted. Again, this is a simple solution if dataset size is irrelevant and computing time won't be an issue.

To report the daily headcount afterwards you could use proc sql summary functions or proc freq and the like with by variables. The main point is that the vertical extension of your data significantly ease up programming difficulty.

   

Vincent

Respected Advisor
Posts: 3,799

Re: Compute and graph the running inventory

Posted in reply to Vince28_Statcan

I like this idea and I've use similar approach too.  I would optimize the data set and keep only ID variable(s) and new date.

data all_animals;
   input id $ (date_arrived date_gone) (:date9.);
   cards;
aba 04JAN2012 .
bab 05May2012 01Dec2012
;;;;
   run;


data all_animals_dailyseries(keep=id newdate);
   set all_animals;
   length newdate 8;
  
format newdate date9.;
  
do newdate = date_arrived to coalesce(date_gone,today());
      output;
     
end;
  
run;
Ask a Question
Discussion stats
  • 9 replies
  • 344 views
  • 6 likes
  • 5 in conversation