topic Re: PROC SQL -group by & max values in SAS Programming

PROC SQL -group by & max values

_LB — Tue, 06 Dec 2011 22:52:50 GMT

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

PROC SQL -group by & max values

art297 — Tue, 06 Dec 2011 23:11:58 GMT

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;

PROC SQL -group by & max values

Ksharp — Wed, 07 Dec 2011 03:42:41 GMT

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

PROC SQL -group by & max values

_LB — Wed, 07 Dec 2011 14:31:28 GMT

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

PROC SQL -group by & max values

_LB — Wed, 07 Dec 2011 14:33:52 GMT

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

Re: PROC SQL -group by & max values

Peter_C — Wed, 07 Dec 2011 15:30:52 GMT

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;

PROC SQL -group by & max values

_LB — Wed, 07 Dec 2011 16:42:11 GMT

Peter,

Works for getting the last month-Thanks!