DATA Step, Macro, Functions and more

PROC SQL -group by & max values

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 170
Accepted Solution

PROC SQL -group by & max values

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
Locationmonthyear
11I102011
8E102011
M10102011
Z4I102011
M07102011
7N102011
M9I102011
13I102011
M6I102011
MIN102011
8E112011
M9I112011
13I112011
11I112011
M07112011
M6I112011
M10112011

Accepted Solutions
Solution
‎12-07-2011 10:30 AM
Valued Guide
Posts: 2,177

Re: PROC SQL -group by & max values

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;

View solution in original post


All Replies
PROC Star
Posts: 7,484

PROC SQL -group by & max values

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;

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL -group by & max values

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

Super User
Posts: 10,041

PROC SQL -group by & max values

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

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL -group by & max values

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

Solution
‎12-07-2011 10:30 AM
Valued Guide
Posts: 2,177

Re: PROC SQL -group by & max values

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;

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL -group by & max values

Peter,

Works for getting the last month-Thanks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 9269 views
  • 0 likes
  • 4 in conversation