[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: SAS Forum: Sum down a column using information from 2 unmerged datasets
From:       S=?UTF-8?Q?=C3=B8ren_Lassen?= <000002b7c5cf1459-dmarc-request () LISTSERV ! UGA ! EDU>
Date:       2017-09-27 11:54:27
Message-ID: 4215882649534282.WA.s.lassenpost.tele.dk () listserv ! uga ! edu
[Download RAW message or body]

Roger,
You can also do this without the dosubl and macro stuff:
proc sql;
  create table means as select 
       mean(have_a.count) as mean,
       have_b.date_start,
       have_b.date_end
  from have_a,have_b
  where have_a.date between have_b.date_start and have_b.date_end
  group by
       have_b.date_start,have_b.date_end
  ;
quit;

(assuming that the dates are converted to SAS dates first).

Regards,
Søren

On Sat, 23 Sep 2017 08:03:55 -0400, Roger DeAngelis <rogerjdeangelis@GMAIL.COM> wrote:

>You can also solve this problem without using macro variables.
>You can use 'common' storage.
>
>On Fri, Sep 22, 2017 at 6:24 PM, Roger DeAngelis <rogerjdeangelis@gmail.com>
>wrote:
>
>> SAS Forum: Sum down a column using information from 2 unmerged datasets
>>
>>    This can more elegantly be done in IML/R or WPS/PROC-R
>>
>>    Here is a SAS solution (DOSUBL is not supported yet in WPS)
>>
>>    Using a dataset with date ranges to summarize a second dataset
>>    without using a joijn or merge.
>>
>>    WORKING CODE
>>
>>       MAINLINE
>>
>>         * read one range at a time;
>>
>>         set have_b;
>>
>>         call symputx(beg,date_start);
>>         call symputx(end,date_end);
>>
>>       DOSUBL
>>
>>         select
>>           put(mean(count),5.1) into :mean
>>         from
>>           have_a
>>         where
>>           date between
>>             input(symget("beg").7.) and input(symget("end").7.)))
>>
>>        MAINLINE
>>
>>          mean=symget('mean');
>>
>>
>> see
>> https://goo.gl/mVyj2V
>> https://communities.sas.com/t5/Base-SAS-Programming/Sum-
>> down-a-column-using-information-from-2-unmerged-datasets/m-p/398228
>>
>>
>> HAVE ( a sequence of ranges and some daily data)
>> =================================================
>>
>>  WORK.HAVE_B total obs=2
>>
>>                      DATE_
>>    Obs    GROUP      START      DATE_END
>>
>>     1       1      01JAN2005    05JAN2005
>>     2       1      02JAN2005    05JAN2005
>>
>>
>>  WORK.HAVE_A total obs=5               |   RULES
>>                                        |
>>   Obs    GROUP    COUNT      DATE      |                      Sum the
>> range anf compute average
>>                                        |
>>    1       1        50     01JAN2005   |  01JAN2005 05JAN2005
>> 50+51+49+34+34=218  218/5 = 43.6
>>    2       1        51     02JAN2005   |  02JAN2005 05JAN2005 51+49+34+34
>>  =168  168/4 = 42.0
>>    3       1        49     03JAN2005   |
>>    4       1        34     04JAN2005   |
>>    5       1        34     05JAN2005   |
>>
>>
>> WANT
>>
>>   WORK.WANT total obs=2
>>
>>                     DATE_
>>   Obs    GROUP      START      DATE_END     MEAN
>>
>>    1       1      01JAN2005    05JAN2005    43.6
>>    2       1      02JAN2005    05JAN2005    42.0
>>
>> *                _              _       _
>>  _ __ ___   __ _| | _____    __| | __ _| |_ __ _
>> | '_ ` _ \ / _` | |/ / _ \  / _` |/ _` | __/ _` |
>> | | | | | | (_| |   <  __/ | (_| | (_| | || (_| |
>> |_| |_| |_|\__,_|_|\_\___|  \__,_|\__,_|\__\__,_|
>>
>> ;
>>
>> data have_a;
>> input group$ count date $10.;
>> cards4;
>> 1 50 01JAN2005
>> 1 51 02JAN2005
>> 1 49 03JAN2005
>> 1 34 04JAN2005
>> 1 34 05JAN2005
>> ;;;;
>> run;quit;
>>
>> data have_b;
>> input group$ date_start $10. date_end $10.;
>> cards4;
>> 1 01JAN2005 05JAN2005
>> 1 02JAN2005 05JAN2005
>> ;;;;
>> run;quit;
>>
>> *          _       _   _
>>  ___  ___ | |_   _| |_(_) ___  _ __
>> / __|/ _ \| | | | | __| |/ _ \| '_ \
>> \__ \ (_) | | |_| | |_| | (_) | | | |
>> |___/\___/|_|\__,_|\__|_|\___/|_| |_|
>>
>> ;
>>
>> data want;
>>
>>    set have_b;
>>
>>    call symputx('beg',date_start);
>>    call symputx('end',date_end);
>>
>>    rc=dosubl('
>>       proc sql;
>>         select
>>           put(mean(count),5.1) into :mean
>>         from
>>           have_a
>>         where
>>           date between symget("beg") and symget("end")
>>       ;quit;
>>    ');
>>
>>  mean=symget('mean');
>>  drop rc;
>> run;quit;
>>
>>
>>
>>
>
[prev in list] [next in list] [prev in thread] [next in thread]