BookmarkSubscribeRSS Feed
hk2013
Fluorite | Level 6

i have a dataset which has different months data i need to sum the same months data and than take the difference the previous month 

 

city          month                  count 

NEV        01-Dec-2018        50

NEV        01-Dec-2018        40

REN       01-Dec-2018         20

REN       01-Dec-2018         80

NEV       01-jan-2019           15

NEV      01-jan-2019             15

REN      01-jan-2019             10

REN      01-jan-2019            10

NEV      01-feb-2019             25

NEV      01-feb-2019             25

REN     01-feb-2019              30

REN     01-feb-2019             20

NEV     01-mar-2019             45

NEV     01-mar-2019              45

REN    01-mar-2019               25

REN    01-mar-2019               25

 

 

output:

 

city          month                  count       diff

NEV        01-Dec-2018        90            

REN       01-Dec-2018         100           

NEV       01-jan-2019           30            -60

REN      01-jan-2019             20           -80

NEV      01-feb-2019             50            20

REN     01-feb-2019              50            30

NEV     01-mar-2019             90            40

REN    01-mar-2019               50            0 

 

 

6 REPLIES 6
PaigeMiller
Diamond | Level 26

PROC SUMMARY will find the sum by month and city. Then you can take the output from PROC SUMMARY, sort it by city and month, then in a data step, use the LAG function to find the differences. 

--
Paige Miller
novinosrin
Tourmaline | Level 20

Hi @hk2013   A good excercise for me to personally practice and test myself 🙂 Thank you for the fun question

 


data have;
input city $          month    :date9.              count  ;
format month date9.;
cards;
NEV        01-Dec-2018        50
NEV        01-Dec-2018        40
REN       01-Dec-2018         20
REN       01-Dec-2018         80
NEV       01-jan-2019           15
NEV      01-jan-2019             15
REN      01-jan-2019             10
REN      01-jan-2019            10
NEV      01-feb-2019             25
NEV      01-feb-2019             25
REN     01-feb-2019              30
REN     01-feb-2019             20
NEV     01-mar-2019             45
NEV     01-mar-2019              45
REN    01-mar-2019               25
REN    01-mar-2019               25
;
 
data want;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("city") ;
   h.definedata ("_count") ;
   h.definedone () ;
   call missing(_count);
end;
do until(last.month);
set have;
by city month notsorted;
sum=sum(sum,count);
end;
if h.find()=0 then  diff=sum-_count; 
h.replace(key:city,data:sum);
keep city month sum diff;
run;
Reeza
Super User

Are you programming or using the GUI in EG?

PGStats
Opal | Level 21

In SQL, that would be:

 

proc sql;
select
    a.city, 
    a.month, 
    a.mCount,
    a.mCount-b.mCount as countChange
from
    (select city, month, sum(count) as mCount 
     from have group by city, month) as a left join
    (select city, month, sum(count) as mCount 
     from have group by city, month) as b 
        on a.city=b.city and a.month=intnx("month", b.month, 1)
order by month, city;
quit;

This will give the correct answer even if there are some missing months.

PG
Ksharp
Super User

If there was not gap in data.

 


data have;
input city $          month    :date9.              count  ;
format month date9.;
cards;
NEV        01-Dec-2018        50
NEV        01-Dec-2018        40
REN       01-Dec-2018         20
REN       01-Dec-2018         80
NEV       01-jan-2019           15
NEV      01-jan-2019             15
REN      01-jan-2019             10
REN      01-jan-2019            10
NEV      01-feb-2019             25
NEV      01-feb-2019             25
REN     01-feb-2019              30
REN     01-feb-2019             20
NEV     01-mar-2019             45
NEV     01-mar-2019              45
REN    01-mar-2019               25
REN    01-mar-2019               25
;
proc summary data=have;
by city month notsorted;
var count;
output out=temp sum=;
run;
data want;
 set temp(drop=_:);
 dif=dif2(count);
run;
singhsahab
Lapis Lazuli | Level 10
data have;
input city $          month    :date9.              count  ;
format month DATE9.;
cards;
NEV        01-Dec-2018        50
NEV        01-Dec-2018        40
REN       01-Dec-2018         20
REN       01-Dec-2018         80
NEV       01-jan-2019           15
NEV      01-jan-2019             15
REN      01-jan-2019             10
REN      01-jan-2019            10
NEV      01-feb-2019             25
NEV      01-feb-2019             25
REN     01-feb-2019              30
REN     01-feb-2019             20
NEV     01-mar-2019             45
NEV     01-mar-2019              45
REN    01-mar-2019               25
REN    01-mar-2019               25
;
RUN;

PROC REPORT DATA=HAVE OUT=WANT22(DROP=_BREAK_);
COLUMN CITY MONTH COUNT MON YR;
DEFINE CITY/GROUP ;
DEFINE MONTH/GROUP;
DEFINE COUNT/SUM;
COMPUTE YR;
MON=MONTH(_C2_);
 YR=YEAR(_C2_);
ENDCOMP;
RUN;
PROC SORT DATA=WANT22;
BY CITY YR MON;
RUN;
DATA WANT (KEEP=CITY MONTH COUNT DIFF);
SET WANT22;
BY CITY YR MON;
DIFF=DIF(COUNT);
IF FIRST.CITY THEN CALL MISSING(DIFF);
RUN;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 918 views
  • 3 likes
  • 7 in conversation