Desktop productivity for business analysts and programmers

How to use Loops in Data manipulation in SAS - creating new columns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

How to use Loops in Data manipulation in SAS - creating new columns

Hi all,

 

First of all I must thankful to everyone there who kept helping me to learn SAS fast. Almost a month ago, I was zero with SAS but now, at least I am feeling confident and have grown my love towards it. Smiley Happy

 

Now, to my problem...

 

Suppose I have the following dataset:

 

data have;
  infile cards truncover expandtabs;
  input MC $ ET $ Date :Date9. Time :time8. Dia KMs;
  format Date date9. Time time8.;
cards;
US0001 Lath 12JAN15 7:00 890.8 2900334.00000
US000328 Lath 13JUL15 0:00 890.2 2232830.00000
US000328 Lath 02JAN16 0:00 885.9 2310299.00000
US000328 Lath 13JUN16 0:00 881.4 2450616.00000
US000329 Lath 12JAN15 0:00 885.7 2235626.00000
US000329 Lath 30MAY15 0:00 880.6 2373070.00000
US000329 Lath 14JAN16 0:00 872.6 2558987.00000
US000329 Lath 05AUG16 0:00 863.1 2740207.00000
US000331 Lath 13JUN15 0:00 860.7 2689105.00000
US000331 Lath 12JUN16 0:00 860.7 2730534.00000
US000334 Lath 02JUL15 0:00 859.6 3034370.00000
US000334 Lath 24JAN16 0:00 853.2 3230211.00000
US000334 Lath 27AUG16 0:00 845.9 3411050.00000
US000335 Lath 17MAR15 0:00 847.3 2183273.00000
US000335 Lath 15JAN16 0:00 847.3 2258321.00000
US000335 Lath 05AUG16 0:00 841.9 2438931.00000
US000336 Lath 16MAR15 0:00 853.6 2365989.00000
US000336 Lath 02MAY15 0:00 850.6 2405547.00000
US000336 Lath 28JAN16 0:00 846.3 2522017.00000
US000336 Lath 14MAR16 0:00 839.2 2554822.00000
;
run;

 

What I want to do, is that for each MC above:

 

1.  Calculate Dia difference in such a way that if there is only one row, keep the dia otherwise DiaDiff = Dia in current row - Dia in previous row (please Note: for practice, I used Case When Else option here, but you still can be better than me, I am sure)

2. If DiaDiff = Dia in that row, Dia_Flag = 0, otherwise =1

3. Difference between the Days in two rows. If first row, DaysDiff = 0, otherwise DaysDiff = Date in current row - Date in previous row

4. Similary for KMs Difference. If first row, KM_Diff = 0, otherwise KM_Diff = KMs in current row - KMs in previous row

 

And result would look like something below:

 

MCETDateTimeDiaKMsDiaDiffDia_FlagDaysDiffKM_Diff
US0001Lath12Jan20157:00:00890,82900334890,8000
US000328Lath13Jul20150:00:00890,22232830890,2000
US000328Lath02Jan20160:00:00885,923102994,3117377469
US000328Lath13Jun20160:00:00881,424506164,51163140317
US000329Lath12Jan20150:00:00885,72235626885,7000
US000329Lath30May20150:00:00880,623730705,11138137444
US000329Lath14Jan20160:00:00872,6255898781229185917
US000329Lath05Aug20160:00:00863,127402079,51204181220
US000331Lath13Jun20150:00:00860,72689105860,7000
US000331Lath12Jun20160:00:00860,727305340036541429
US000334Lath02Jul20150:00:00859,630343701,1100
US000334Lath24Jan20160:00:00853,232302116,41206195841
US000334Lath27Aug20160:00:00845,934110507,31216180839
US000335Lath17Mar20150:00:00847,32183273847,3000
US000335Lath15Jan20160:00:00847,322583210030475048
US000335Lath05Aug20160:00:00841,924389315,41203180610
US000336Lath16Mar20150:00:00853,62365989853,6000
US000336Lath02May20150:00:00850,62405547314739558
US000336Lath28Jan20160:00:00846,325220174,31271116470
US000336Lath14Mar20160:00:00839,225548227,114632805

 

 

Currently I am learning loops and hence this case.

 

Thanks.


Accepted Solutions
Solution
‎11-30-2016 07:54 AM
Super User
Posts: 7,475

Re: How to use Loops in Data manipulation in SAS - creating new columns

Use the lag() function and first. condition to reset for every first observation in a group

data want;
set have;
by MC;
diadiff = dia - lag(dia);
daysdiff = date - lag(date);
km_diff = kms - lag(kms);
if first.MC
then do;
  diadiff = dia;
  daysdiff = 0;
  km_diff = 0;
end;
run;

If you want your difference values to always be positive, use the abs() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,736

Re: How to use Loops in Data manipulation in SAS - creating new columns

Hi,

 

Whilst I don't have time to do all that for you, some tips to get you started:

Use a datastep with a by group.

Use lag

 

E.g.

data want;
  set have;
  by mc;
  retain diadiff dia_flag daysdiff km_diff;
  if first.mc then do;
    diadiff=dia;
    dia=0;
    ...;
  end;
  else do;
    dia_flag=1;
    daysdiff=date-lag(date);
    ...;
  end;
run;

Note, I will repeat, its not a good idea to call variables type names e.g. date or time.

Solution
‎11-30-2016 07:54 AM
Super User
Posts: 7,475

Re: How to use Loops in Data manipulation in SAS - creating new columns

Use the lag() function and first. condition to reset for every first observation in a group

data want;
set have;
by MC;
diadiff = dia - lag(dia);
daysdiff = date - lag(date);
km_diff = kms - lag(kms);
if first.MC
then do;
  diadiff = dia;
  daysdiff = 0;
  km_diff = 0;
end;
run;

If you want your difference values to always be positive, use the abs() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 87

Re: How to use Loops in Data manipulation in SAS - creating new columns

Thanks Kurt,

Only check in the above is, we need to use:
diadiff = lag(dia) - dia;

Otherwise the results will be negative numbers, if we use below:
diadiff = dia - lag(dia);
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 243 views
  • 2 likes
  • 3 in conversation