BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Keith0001
Calcite | Level 5

Hi all,

I'm new at using SAS and am not proficient in BASE SAS. I normally use Proc SQL for my needs.

However, it seems the LAG function is not available for Proc SQL so I'm having to use Base, which is giving me problems.

I'm trying to calculate a running sum of a value over an ID.

This is my example dataset:

data INPUT;

  input ID $2. LINENR VALUE;

  datalines;

A 1 1

A 2 5

A 3 2

A 4 8

B 1 4

B 2 3

B 3 4

C 1 2

D 1 7

E 1 6

E 2 3

E 3 1

E 4 7

E 5 4

F 1 2

G 1 8

G 2 9

;

run;

The output I'm looking for would be in Variable1.

IDLINENRVALUEVAR1
A111
A256
A328
A4816
B144
B237
B3411
C122
D177
E166
E239
E3110
E4717
E5421
F122
G188
G2917

I have already sorted my dataset by ID and Linenumber.

My code is :

data OUTPUT;

  set INPUT;

 

  if ID = lag1(ID) then VAR1 = VALUE + lag1(VALUE) ;

  else VAR1 = VALUE ;

  if ID = lag1(ID) then VAR2 = VALUE + lag1(VAR2) ;

  else VAR2 = VALUE ;

run;

A simpel lag value adds the previous value to the current value for an ID, in VAR1.  But somehow the 2nd row of the output shows an empty value. Why is this ?

As I am looking for a running sum within an ID I also need to add the other values.  VAR2 is an attempt to do this. But it seems I cannot reference the variable itself.

How do I fix my code so I have a running sum within an ID value ?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

data want;

set input;

by id;

if first.id then var1=0;

var1+value;

run;

View solution in original post

5 REPLIES 5
stat_sas
Ammonite | Level 13

data want;

set input;

by id;

if first.id then var1=0;

var1+value;

run;

Keith0001
Calcite | Level 5

Thank you stat@sas , your answer works.

Mohamed, thank you. However your answer gives the wrong result. Its only adding the current record and the previous one per ID.

mohamed_zaki
Barite | Level 11

Regarding your desired output

data OUTPUT (drop=x);

  set INPUT;

  by ID;

x=lag1(Value);

If first.ID then VAR1=VALUE;

Else VAR1 = x + Value ;

run;

Ksharp
Super User

You also could get it by SQL ,since you have already COUNT variable LINENR .

data INPUT;
  input ID $2. LINENR VALUE;
  datalines;
A 1 1
A 2 5
A 3 2
A 4 8
B 1 4
B 2 3
B 3 4
C 1 2
D 1 7
E 1 6
E 2 3
E 3 1
E 4 7
E 5 4
F 1 2
G 1 8
G 2 9
;
run;
proc sql;
 create table want as
  select *,(select sum(value) from input where id=a.id and linenr le a.linenr) as var1
   from input as a;
quit;

Xia Keshan

Keith0001
Calcite | Level 5

Hi Xia,

Thank you, this works very well for my requirements.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 8897 views
  • 0 likes
  • 4 in conversation