PROC SQL -group by & max values

[_LB]

Hello all-

Hope everybody had a good holiday!

I have a group of data that consistes of hospital wards, the year and month in separate columns, and some other various data.

In total there are now 10 units. What I want to do is combine the last year/month into a date value that has all the units accounted for.

So that,

For October, all the units were accounted for, but in November, only 7 units have entries thus far so what I want to do is use the October data in this case until all the November data is accounted for.

The query so far I have that successfully creates a date..everything I have tried results in a SELECT or HAVING error.

proc sql;

select max(input(compress(put(a.year,8.)||put(a.month,8.)),YYMMN6.)) as month2 format MONYY5.

from DEVICE_DAYS as A;

quit;

Some data is below.

Any suggestions greatly appreciated-

Lawrence

Query1

Location	month	year
11I	10	2011
8E	10	2011
M10	10	2011
Z4I	10	2011
M07	10	2011
7N	10	2011
M9I	10	2011
13I	10	2011
M6I	10	2011
MIN	10	2011
8E	11	2011
M9I	11	2011
13I	11	2011
11I	11	2011
M07	11	2011
M6I	11	2011
M10	11	2011

[art297]

Not sure if I understand correctly, but I think that you are looking for something like:

data have;

  input Location $ month year;

  cards;

11I          09          2011

8E          09          2011

M10          09          2011

Z4I          09          2011

M07          09          2011

7N          09          2011

M9I          09          2011

13I          09          2011

M6I          09          2011

MIN          09          2011

11I          10          2011

8E          10          2011

M10          10          2011

Z4I          10          2011

M07          10          2011

7N          10          2011

M9I          10          2011

13I          10          2011

M6I          10          2011

MIN          10          2011

8E          11          2011

M9I          11          2011

13I          11          2011

11I          11          2011

M07          11          2011

M6I          11          2011

M10          11          2011

M07          12          2011

M6I          12          2011

M10          12          2011

M07          12          2012

M6I          12          2012

M10          12          2012

;

proc sql;

  create table want as

    select *

      from (select *

              from have

                   group by month,year

                     having count(*) eq 10)

        having year=max(year) and month=max(month)

  ;

quit;

[_LB]

Art-I think you are getting there but when I ran the code it didn't seem to function properly (or it return no values)

After some fiddling I came up with this..

proc sql;

select sum(1) as counter, input(compress(put(a.year,8.)||put(a.month,8.)),YYMMN6.) as month2

INTO :count1, :month1

from CHILLE.DEVICE_DAYS as A

group by calculated month2

having counter=10 and Max(year) and max(month)

ORDER BY month2 DESC;

quit;

That gives me the last month in a macro variable that I need. Not the most elegant solution but works for now..

Thanks for your help.

Lawrence

[Ksharp]

Maybe you need this:

data DEVICE_DAYS;
  input Location $ month year;
  cards;
11I          09          2011
8E          09          2011
M10          09          2011
Z4I          09          2011
M07          09          2011
7N          09          2011
M9I          09          2011
13I          09          2011
M6I          09          2011
MIN          09          2011
11I          10          2011
8E          10          2011
M10          10          2011
Z4I          10          2011
M07          10          2011
7N          10          2011
M9I          10          2011
13I          10          2011
M6I          10          2011
MIN          10          2011
8E          11          2011
M9I          11          2011
13I          11          2011
11I          11          2011
M07          11          2011
M6I          11          2011
M10          11          2011
M07          12          2011
M6I          12          2011
M10          12          2011
M07          12          2012
M6I          12          2012
M10          12          2012
;
run;

proc sql;
select max(mdy(a.month,1,a.year)) as month2 format MONYY5.
from DEVICE_DAYS as A;
quit;

Ksharp

[_LB]

KSharp-

Thanks for your help, however what I am looking for is the month when all units are entered in the database (10 in total)

so that if October has all 10 entries but November only has seven present, the value returned would be October.

Hope this helps frame the problem.

Thanks again.

Lawrence

[Peter_C · Accepted solution]

did you want a monYYYY date in a macro variable for the latest month in which there are 10 rows ?

try

proc sql noprint ;

    select  max(period) format=monyy7.

      into :latest_full_period

      from (

            select mdy( month,1,year)  as period

              from have

          group by month,year

            having count(*) eq 10

           )

;

%put (latest_full_period)=(&latest_full_period) ;

quit;

[_LB]

Peter,

Works for getting the last month-Thanks!